how to clear data in column formula when it equals $0.00

pris

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?

JamesB

@pris

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)

pris

@JamesB

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)

pris

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

JamesB

@pris

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)

pris

@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

pris

@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

JamesB

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

pris

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

pris

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

JamesB

@pris You should not need the contains argument.

=IF([Processing Fee]@row="Yes", 0.05 * Subtotal@row, "WAIVED")

pris

Thank you @JamesB !