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
 Smartsheet Customer Resources
 62.2K Get Help
 359 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!