Zero Formula

Nancy Heater

I'm trying something similar as mentioned above, but having trouble with it. When I use this, its removing everything, not just the zero results.

IF(SUM([CORN QTY]@row + [PEAS QTY]@row + [CARROTS QTY]@row)=0,"")

Thanks!

Paul Newcome

@Nancy Heater You need to tell the formula what to do if it is not equal to zero.

IF(SUM([CORN QTY]@row + [PEAS QTY]@row + [CARROTS QTY]@row)=0,"", SUM([CORN QTY]@row + [PEAS QTY]@row + [CARROTS QTY]@row))

James Christiansen

@Paul Newcome , I have tried the =if(formula,"",formula) and it works fine to produce a blank rather than a zero. But, the sum (I then have multiple columns whos sums are added) looks like it is formatted as text, the number is moved to left justified, and loses the dollar sign. Then when I try to sum those two values, rather than adding, it appends, so 405+40 results in 40540 rather than $445.

I have five "function" roles that have different rates, and they sum up using this formula:

=IF([Function 1 total]@row + [Function 2 total]@row + [Function 3 total]@row + [Function 4 total]@row + [Function 5 total]@row = 0, "", [Function 1 total]@row + [Function 2 total]@row + [Function 3 total]@row + [Function 4 total]@row + [Function 5 total]@row)

returns this:

You can see the 405 is now left justified and no longer has the dollar sign.

Then I have 4 "equipment" selections at different rates, and they sum up using this formula:

=IF([Equipment 1 total]@row + [Equipment 2 total]@row + [Equipment 3 total]@row + [Equipment 4 total]@row = 0, "", [Equipment 1 total]@row + [Equipment 2 total]@row + [Equipment 3 total]@row + [Equipment 4 total]@row)

returns this:

Both of those formulas display values that make sense, but when I try to add them together

=IF([Function total]@row + [Equipment total]@row = 0, "", [Function total]@row + [Equipment total]@row)

returns this:

I get 40540, the two values appended as though they are text.

Help?

Paul Newcome

@James Christiansen That is very odd. Are you able to provide screenshots of the formulas in the sheets similar to the screenshot below?

James Christiansen

@Paul Newcome , here you go:

Input formula 1-

Input 2-

Input 3-

Input 4-

Input 5-

All get summed by this-

The result of this sum is the first value that looks like text- left justified and lost the $-

Then four more inputs from a separate category-

These are summed-

This result also looks like text:

Then the two category totals are summed:

And that produces a value that looks like two text values joined:

Paul Newcome

Your [Function 1 Total] through [Function 5 Total columns... Do they look right? Are they numbers or text?

James Christiansen

@Paul Newcome

Here you go-

It is only the sums that look like text, the products are fine, maintain the formatting and functionality of currency. You can see the row below where I have not implemented the IF statement to produce blanks.

Paul Newcome

Is that a space between your quotes for outputting a blank if zero? If so, try removing it so that

" "

becomes

""

James Christiansen

@Paul Newcome , I checked each formula, no, there are no spaces between the quotes.

Paul Newcome

Ok. What happens if we do this...

Instead of saying

[Equipment 1 totals]@row + [Equipment 2 Totals]@row + [Equipment 3 Totals]@row

try this:

SUM([Equipment 1 Totals]@row, [Equipment 2 Totals]@row, [Equipment 3 Totals]@row)

James Christiansen

@Paul Newcome That did it, Thanks!

Paul Newcome

@James Christiansen Happy to help. 👍️

Naina Dave

Hello,

I am trying to revise this formula, so that I can hide $0.00 values and need help please. My formula is :

=IF([Actual Spend (POs Completed)]@row <> "", [Budget Planned]@row - [Actual Spend (POs Completed)]@row, [Budget Planned]@row - [Budget Revised]@row)

thank you

Naina

Paul Newcome

@Naina Dave You are going to want to use the same method mentioned earlier in the thread.

=IF(original_formula = 0, "", original_formula)

Basically this says that if the original formula equal zero, output a blank, otherwise output the results of the original formula. You would just need to drop your original formula in where appropriate (excluding the initial "=").

Naina Dave

thank you @Paul Newcome - going to try it now