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!