How to remove the zero in a formula cell

Options

I have a sheet that I'm inputting data in using a form, and then generating a document to print. Not all columns/cells will have an input on every instance, however, every cell is accounted for within the document mapping. The issue I'm running into is I have a column that has a column formula, and every time I have a new entry it automatically returns a 0 in the column. It normally wouldn't be a problem but now it inputs that 0 into the document when generating, when it should be blank in the document. Does anyone have a suggestion on how to remove the 0 or a workaround?

Sorry this was a little hard to explain so please feel free to ask clarifying questions.

Thank you in advance.

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Is this what you are looking for?

    =IF(([Charge Code (Eng/Develop) 2]@row + [Charge Code (Measure A Road Maint) 2]@row + [Charge Code (Gas Tax Road Maint) 2]@row + [Charge Code (CDGB) 2]@row + [Charge Code (PW Operations) 2]@row) > 0, ([Charge Code (Eng/Develop) 2]@row + [Charge Code (Measure A Road Maint) 2]@row + [Charge Code (Gas Tax Road Maint) 2]@row + [Charge Code (CDGB) 2]@row + [Charge Code (PW Operations) 2]@row), "")

Answers

  • Jaime M.
    Jaime M. ✭✭✭
    Options

    Hey Dave!

    It sounds like a good workaround might be to include an IF statement in your formula so indicate "N/A" (or something else) when the field would otherwise appear blank, so when it populates in your document it pulls in "N/A" instead of assuming it to be "0".

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Options

    I would need to see the formula in the offending column to know for sure, but the general idea would be to enclose the existing formula within an IF() statement that returns an empty cell in the cases where you are currently getting zeroes.

  • Dave French
    Dave French ✭✭✭
    Options

    Thank you both. I really need the cell to be blank if possible. Below is the existing formula. It's a simple addition formula.


    =[Charge Code (Eng/Develop) 2]@row + [Charge Code (Measure A Road Maint) 2]@row + [Charge Code (Gas Tax Road Maint) 2]@row + [Charge Code (CDGB) 2]@row + [Charge Code (PW Operations) 2]@row

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓
    Options

    Is this what you are looking for?

    =IF(([Charge Code (Eng/Develop) 2]@row + [Charge Code (Measure A Road Maint) 2]@row + [Charge Code (Gas Tax Road Maint) 2]@row + [Charge Code (CDGB) 2]@row + [Charge Code (PW Operations) 2]@row) > 0, ([Charge Code (Eng/Develop) 2]@row + [Charge Code (Measure A Road Maint) 2]@row + [Charge Code (Gas Tax Road Maint) 2]@row + [Charge Code (CDGB) 2]@row + [Charge Code (PW Operations) 2]@row), "")

  • Dave French
    Dave French ✭✭✭
    Options

    That worked.

    Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!