Date format of 0 and if statements

  • Thread starter Thread starter brent
  • Start date Start date
B

brent

I have an is statment as such:

=IF('Raw Data'!A5=0,,'Raw Data'!A5)

This issue I have is when I format fields as "date" and the
reference cell = 0 excel formates the cell as 01/00/00.

I have also used the formula:
=IF('Raw Data'!A5=0,"",'Raw Data'!A5)

The issue I have is I want to create graphs of this data in
a dynamic fashion, but when the data range of the graph
encounters either "" or 1/00/00 as a date, it plots it as a
data point. I need a way to make my IF statment to produce
a BLANK or EMPTY cell. Is there a key word in excel for this?
 
You can use NA() to give a point that won't be plotted on a graph. If using a
line graph though this will not work with a stacked graph, eg:-

=IF('Raw Data'!A5=0,NA(),'Raw Data'!A5)

Downside is that if you are summing these ranges, then the error will kill that,
so you need to account for it in any calculations
 
A cell will never be "empty" when it contains a formula,
but you can use NA() to overcome your graphing issue:

=IF('Raw Data'!A5=0,NA(),'Raw Data'!A5)

HTH
Jason
Atlanta, GA
 
Brent,
If you use Jason's suggestion to fix your graphing problem you will have #N/A showing up in your table. If you want to keep your table clean you can set up a conditional format to change the font to white when the cell contains #N/A. This way the NA error will prevent the cell from graphing, and the white font will prevent it from showing in your table.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Jason Morin wrote: -----

A cell will never be "empty" when it contains a formula,
but you can use NA() to overcome your graphing issue:

=IF('Raw Data'!A5=0,NA(),'Raw Data'!A5)

HTH
Jason
Atlanta, GA
 
Back
Top