formula in cell, how to show blank if 0

[fnaclew]

Hi All,

Firstly, sorry but there appears to be a problem with the search function!

I have a spreadsheet which imports data from worksheets week 1, 2 etc to a monthly summary.
Is there any way I can show a cell as blank if the return equals 0?

I'm sure the resolution is probably simple but then so am I.

Any help or advice will be greatly appreciated.

Many thanks for your time and consideration.

[NBVC]

Option 1:

Go to Tools|Options and in the View tab uncheck Zero Values

Option 2: Custom Format cells to : 0;-0;;@

Option 3: Incorporate in Formula, e.g. =IF(Formula=0,"",Formula) where Formula is your actual formula

[fnaclew]

Hi NBVC,

Thank you.

Options 2 & 3 I don't fully understand however option 1 appears to work so that's brilliant.

Hopefully I can eventually acquire the knowledge to understand options 2 & 3.

Again, many thanks.

All the best.

[zbor]

Go to first post -> edit -> Go advanced and make thread as [solved]

[fnaclew]

Go to first post -> edit -> Go advanced and make thread as [solved]

Blimey, givvus a chance!

Yep, was about to but chaps came back to depot for lunch so was otherwise engaged.

Cheers.

[zbor]

no mercy

[NBVC]

For option 2, you would select the cell(s) to "hide zeroes" from and go to Format|Cells and then select Custom from the categories on the left... then paste the format I showed in the Type field...

For option 3, you would alter your existing formula(s) to include the "0" trap....

e.g. If your current formula is =A1*B1 and you want to hide the result of 0, if it occurs, then you would change the formula to: =IF(A1*B1=0,"",A1*B1). This says if A1*B1 is 0 then return a null (leave cell blank) otherwise return the actual result.

Hope that helps.

[Ethere]

I know this thread is extremely old, but it's right up my alley. I am trying to work with a formula that has nested IF statements. The formula I'm using is returning the correct values, but what I also want it to do is return a blank cell if the value in I3 is 0. Any suggestions? Here's my formula:

Please Login or Register  to view this content.

Any help you can all provide I would appreciate.

[FDibbins]

Hi Ethere, welcome to the forum

yes, it is old, and unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.