can a column formula produce a number or a word with the value of 1

pris

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?

pris

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!

heyjay

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 with Waived - 1 string.

Fee Amount = IF( [Processing Fee]@row = "YES" ,  0.05 * Subtotal@row , 'Waived - 1' )

SteyJ

=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)

pris

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.

pris

@SteyJ

=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

SteyJ

https://community.smartsheet.com/discussion/comment/421804#Comment_421804

Use this formula in the total column:

=IF([Processing Fee]@row = "WAIVED", Subtotal@row, Subtotal@row + [Fee Amount]@row)

pris

@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

pris

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

pris

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!