Zero Formula

Ryan Coles

Where do I find the formula to have a cell display nothing when the total becomes the number zero? I have a sheet with zeros in multiple places and it clutters to much. 

Andrée Starå

Hi,

Do you want to change the color of the zero or make the cell empty?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Ryan Coles

HI,

    Thank you for your response! I would like to make the cell empty.

Paul Newcome

Unfortunately you can only have either manual entry OR a formula. If you have a formula entered and manually update the cell, the formula will be erased.

 

If you are using a formula to do calculations to populate the cells, then wrapping the whole thing in an additional IF statement would work.

 

=IF(..........Original Formula........... = 0, "")

 

If you are using manual entry, while you can't make it blank, you can use conditional formatting to make it appear blank by setting the font color to change to the background color of the cell if it changes to zero. This would effectively "hide" the zeroes and clean up your sheet.

Andrée Starå

Happy to help!

Paul beat me to it! 

Have a fantastic week!

Best,

Andrée

Shawn R

Not sure what your formula is but I was able to accomplish something similar. Essentially I wanted two fields to be blank after a date field in that row was in the past. So I built a formula to calculate todays date against the date the row was submitted. If the date the row was submitted was in the past I told it to populate it with the contents of my column called 'Delete'

I just never put anything in the delete column so it essentially deleted the contents of that field.

Marije

Hi there,

If I use the formula to get a blank field when the result of the formula is 0 I get an error: Invalid data type.

The formula is:

=IF(SUM({Open Positions - Per Employee Group Range 6}, {Open Positions - Per Employee Group Range 7}, {Open Positions - Per Employee Group Range 8}, {Open Positions - Per Employee Group Range 9}, {Open Positions - Per Employee Group Range 10}, {Open Positions - Per Employee Group (SG&A  Range 2}), =0, "")

*weird enough the formula does work when I try it on excisting formulas that already have a 0 as a result.

Can someone help me?

Thanks,

Marije

Paul Newcome

You have an extra comma tucked in that isn't needed.

Try this...

=IF(SUM({Open Positions - Per Employee Group Range 6}, {Open Positions - Per Employee Group Range 7}, {Open Positions - Per Employee Group Range 8}, {Open Positions - Per Employee Group Range 9}, {Open Positions - Per Employee Group Range 10}, {Open Positions - Per Employee Group (SG&A Range 2}) = 0, "")

Rebecca Copley

Hello,

I'm trying to do this with a =SUM(children()) formula. If there are no children, I want the cell to be blank instead of carrying a zero.

But, wrapping the existing formula as mentioned above is not working: =IF(=SUM(CHILDREN()) = 0, "")

Thanks,

Rebecca

Paul Newcome

@Rebecca Copley You are going to need to remove the = before SUM and then insert the SUM(CHILDREN()) into the 3rd portion of the IF statement.

=IF(SUM(CHILDREN()) = 0, "", SUM(CHILDREN()))

Rebecca Copley

Thank you, Paul! Does this mean that any cells that are reading from those blank cells would show as Invalid? Does it have to have a zero for associated formulas to calculate?

Paul Newcome

@Rebecca Copley That would depend on the formula(s) you are using to reference those cells. If you would like to provide some examples of the formulas you are using, I would be happy to give my opinion on whether or not I think they would be affected by a zero vs a blank.

Rebecca Copley

@Paul Newcome They are simple subtraction formulas - "this cell" minus "that cell."

Paul Newcome

In those cases I believe blanks are treated as zeros.

Jill Belnap

I have a formula: =SUM([Jan 21]2:[Jan 21]6)

So it is adding 5 cells. I Added the formula =IF(=SUM([Feb 21]2, :[Feb 21]6)= 0, "") and get an error message.

Paul Newcome

=IF(SUM([Feb 21]2:[Feb 21]6) = 0, "", SUM([Feb 21]2:[Feb 21]6))

Try that one.