Excel question

  handful 16:57 22 Dec 05
Locked

I have a spreadsheet set up with simple sum formulas but would like to know if there is any way that the cells containing the formula could display nothing, i.e. remain totally blank when there is no data to be calculated. Displaying zeros when there is nothing to calculate makes the printed version look very untidy and it would be great if this could be done. Any help would be much appreciated.

  VoG II 17:03 22 Dec 05

Let's say you are adding A1 and B1

=A1+B1

but you don't want to display zeros. Use the following formula instead:

=IF(A1+B1 <> 0, A1+B1, "")

  PaulB2005 17:20 22 Dec 05

I always thought zeros looked neater than blank cells.

For cells range C1 to C4 use

=IF(SUM(C1:C4)=0,"",SUM(C1:C4))

If other words

If the sum of C1 to C4 =0 then show "" (blank) otherwise show the total of C1 to C4.

  PaulB2005 17:21 22 Dec 05

Does this site keep crashing?

  beynac 17:21 22 Dec 05

You can hide zero values. Go to Tools/Options and untick "Zero values" on the View tab.

  handful 17:38 22 Dec 05

Thanks for all the replies and PaulB2005, yes the site is crashing which is why I didn't respond sooner. I actually thought of another way as well just after posting, by conditional formatting the cell to white if value equals 0. Thanks again to you all and I'll take the opportunity to wish everyone a very happy Christmas. Keep up the good work!

  PaulB2005 17:51 22 Dec 05

Even with the site crashing you still can't beat VoG to the answer on an Excel question.....

;-)

  Simsy 18:00 22 Dec 05

will work ok. The only thing to consider about the conditional formatting method is that it might go awry if the file is opened in another application...

If you are only using it yourself, then no problem, but if you need to share it with someone who has, for example, Works, or Ability, or 602, it might be that the conditional formatting doesn't work, whereas the formula method would. (I do know that the conditional formatting works ok when opened in OpenOffice.)

An advantage of using the conditional formatting method is that, in absolute terms, it's probably quicker to calculate. Of course, in the real world, unless your spreadsheet is MASSIVE, this isn't going to be noticeable, (and even if it is MASSIVE it will probably be barely noticable!) But, depending on what the actual formulae are, it is conceivable that some cells might return different results depending on whether the value of other cells are 0, (zero), or "", (blank). I should add that this is also unlikely... but might merit consideration if you get unexpected results!

Good luck,

Regards,

Simsy

  seedie 20:06 22 Dec 05

TRY USIng IF(ISBLANK) use help for guidance

CD

  VoG II 20:37 22 Dec 05

One of the fascinations of Excel, for me, is that for most issues there are more methods available than ways to skin a cat. This may be one of the reasons why some people find it a bit tricky - there are just so many options.

My preference would always be to use a formula-based approach for this type of problem. Conditional Formatting and hiding zero values have their place but - say in a year's time - would you remember what you had done and be able to figure out why a cell sometimes displayed a number and was sometimes blank? At least with a formula, in this case you could work out what was going on.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…