how to clear data in column formula when it equals $0.00
I have a sheet that collects information to create an invoice. The quantity, unit price, description and line total for up to three items. The quantity and unit price are set to automatically be multiplied in the line total set as a column formula, =[Unit Price 2]@row * [Quantity 2]@row. The problem I am finding, is that if only one line is needed, the 0.00 for line 2 and three are still being mapped into the document because the column is not blank. I am looking for the column to clear if the total is $0.00. Is there a formula for this?
Answers

If I am understanding your ask. This should work.
=if([Unit Price 2]@row * [Quantity 2]@row=0," ",[Unit Price 2]@row * [Quantity 2]@row)

Unfortunately that didn't work. Here is a little more context. I need the line totals to produce totals when in use. If they are not in use, I need the cell to be clear so that it doesnt not enter $0.00 on the invoice when the line is not in use. I also have the dropdown question that asks if additional lines are needed, I tried to create an automation that clears the cell value in line total 2 if the additional line 2 says no, but because its a column formula it doesnt allow it.
Line Total 1 formula is =[Unit Price 1]@row * [Quantity 1]@row
Line Total 2 formula is =[Unit Price 2]@row * [Quantity 2]@row
3 line total formula is =[Quantity 3 ]@row * [Unit 3]@row
Subtotal formula is =[Line Total 1 ]@row + [Line Total 2]@row + [3 line total]@row
Fee Amount formula is =IF(CONTAINS("Yes", [Processing Fee]@row), 0.05 * Subtotal@row, IF(CONTAINS("No", [Processing Fee]@row), "WAIVED"))
Total =SUM(Subtotal@row + [Fee Amount]@row)

@SteyJ thank you so much for your help on the other problem

Line Total 2 formula, to be cleared if Additional line 2 = No
=if([Additional Line 2]@row="No"," ", [Unit Price 2]@row * [Quantity 2]@row)

@JamesB That worked but it removed the USD currency structure so $375 is now reflecting 375. Is it possible to maintain the $375.00 structure

@JamesB Thank you so much for your help! I can make adjustments to the PDF rather than trying to get it perfect on the sheet

@pris You should be able to highlight the column and choose currency from the top toolbar.

I think the issue is bigger than the USD currency missing. The subtotal adds line 1, 2 and 3 total together, but when there is nothing in line 2 or 3, it shows as a number rather than USD. If there is a precessing fee, of 5%, the fee amount and total are becoming invalid

@JamesB it gives an invalid operation Fee amount formula is =IF(CONTAINS("Yes", [Processing Fee]@row), 0.05 * Subtotal@row, IF(CONTAINS("No", [Processing Fee]@row), "WAIVED"))
and total formula is =IF([Fee Amount]@row = "WAIVED", Subtotal@row, Subtotal@row + [Fee Amount]@row)

@pris You should not need the contains argument.
=IF([Processing Fee]@row="Yes", 0.05 * Subtotal@row, "WAIVED")

Thank you @JamesB !
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!