Search This Blog

Friday, June 20, 2008

Little excel tips: display no value for zero, automatically add date

So, you're totalling a formula up (such as a sum) and you do not want the value to display as 0 (i.e., zero). In my case, some people will use the spreadsheet online, while others will print it out. For those who print it, I want the total column to appear blank (but it is not!) so that they can manually print in their calculations (sure, they should be using it online, but....)

To display a blank value instead of zero, use this bit of code modified for your function (in my example, sum) and values (my case, a range of cells C3 throughH3):

=IF(SUM((C3:H3))>=1,SUM((C3:H3)),"")

Nifty, hmm?

Now, to add the date, each time a spreadsheet is opened or saved:
In a cell:
=NOW()

Ah, but you only want the year and month, e.g., June-01
Right click on the cell, format> on the number tab, choose> Date Now choose the date as you would like it to format. Need the time? Throw that in, too!

2 comments:

Anonymous said...

Nice. But, unfortunately, if you are charting the data the "" blank still looks like 0 to the chart code. I'm looking for something the chart code would treat like a blank cell.

robin said...

Hi there,
Thanks for letting me know. I hadn't thought about what happens if you take the data and pull it through to a graph or chart. I think there are a few ways around this using either some scripting or a filter.

Try this:
Select your chart > Tools > Option > Chart, Plot Empty Cells

(choose: not plotted)