can a column formula produce a number or a word with the value of 1
I have a sheet we are using to create invoices. The form asks if there is a processing fee, if yes, the fee column has the following formula =IF(CONTAINS("Yes", [Processing Fee]@row), 0.05 * Subtotal@row). But I need the formula to also account for if someone answers no to a processing fee, the FEE AMOUNT needs to say WAIVED and have a value of 1 so that it can be multiplied with the subtotal and produce the total without a fee. Is there a formula for this?
Best Answer

I changed the forumla to =IF([Fee Amount]@row = "WAIVED", Subtotal@row, Subtotal@row + [Fee Amount]@row) and that did the trick @SteyJ Thank you for your help!
Answers

Assuming that the formula is on the Fee Amount Column, and that the Processing fee column is a dropdown to show only Yes or No.
Fee Amount = IF( [Processing Fee]@row = "YES" , 0.05 * Subtotal@row , 1 )
This will show 1 to the column.
I am not sure how you want to show both 'value of 1' and 'Waived' on the same cell. Unless you replace the
1
withWaived  1
string.Fee Amount = IF( [Processing Fee]@row = "YES" , 0.05 * Subtotal@row , 'Waived  1' )
...

=IF(CONTAINS("Yes", [Processing Fee]@row), .05 * Subtotal@row, IF(CONTAINS("No", [Processing Fee]@row), "WAIVED")
You will want to edit the formula total amount column for if FEE AMOUNT contains "WAIVED".
=IF([FEE AMOUNT]@row = "WAIVED", 1 * Subtotal@row, [FEE AMOUNT]@row + Subtotal@row)
Sincerely,
Jacob Stey

I made a mistake, WAIVED should equal 0. Because I have a total column using a column formula that is taking the sum of the subtotal and the fee amount, the above formulas are not working. I need the word waived to appear in the fee amount if it is selected No to the processing fee, but I need the word WAIVED to have a value of 0 so that when the fee amount and subtotal are added together in the total column, it produces the correct number. The columns are being mapped to a PDF creation to send out invoices.

=IF(CONTAINS("Yes", [Processing Fee]@row), 0.05 * Subtotal@row, IF(CONTAINS("No", [Processing Fee]@row), "WAIVED"))
This forumula is almost giving the desired results, but is there a way to modify so that WAIVED has a value of zero?
Because i have a total column that is adding together the subtotal and fee =SUM(Subtotal@row + [Fee Amount]@row)
But when it says WAIVED, my total changes to zero

Use this formula in the total column:
=IF([Processing Fee]@row = "WAIVED", Subtotal@row, Subtotal@row + [Fee Amount]@row)
Sincerely,
Jacob Stey

@SteyJ That worked, you are amazing! Thank you. I am also having trouble with another aspect. Perhaps you can help? I added you as a comment, on the other post

@SteyJ I thought it was working but when the fee amount is waived the total is coming up as 750WAIVED rather than $750

I changed the forumla to =IF([Fee Amount]@row = "WAIVED", Subtotal@row, Subtotal@row + [Fee Amount]@row) and that did the trick @SteyJ Thank you for your help!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!