Zero Formula

12/17/18 Edited 12/09/19
Answered - Pending Review

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. 

Previous13

Answers

  • Andrée Starå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

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

  • Paul NewcomePaul 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.

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    Paul beat me to it! wink

    Have a fantastic week!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

  • MarijeMarije
    edited 04/02/20

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

    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

  • Paul NewcomePaul 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()))

    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?

  • Paul NewcomePaul 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.

    thinkspi.com

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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

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


    Try that one.

    thinkspi.com

Sign In or Register to comment.