Zero Formula
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 EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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.

Happy to help!
Paul beat me to it!
Have a fantastic week!
Best,
Andrée
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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

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.

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

In those cases I believe blanks are treated as zeros.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!