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.

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 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.

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.

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.

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, "")

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?

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

## Answers

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

SMARTSHEET PARTNER & CONSULTANT / EXPERTAndrée Starå| Workflow Consultant / CEO @WORK BOLDW: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.HI,

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

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.

thinkspi.com

Happy to help!

Paul beat me to it!

Have a fantastic week!

Best,

Andrée

SMARTSHEET PARTNER & CONSULTANT / EXPERTAndrée Starå| Workflow Consultant / CEO @WORK BOLDW: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.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.

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

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, "")

thinkspi.com

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

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

thinkspi.com

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?

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

thinkspi.com

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

In those cases I believe blanks are treated as zeros.

thinkspi.com

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.

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

Try that one.

thinkspi.com