use a formula to create a empty cell

I am plotting weekly averages on a line graph in Excel XP. I enter the weekly
numbers above a cell (g30) containing this formula:
=if(count(g23.g29)=0,"",average(g23.g29))
and plot that result against the date in cell g31. The next week the data
goes into the range h23.h29, the averaging formula is in cell h30, and the
date is in cell h31, etc.
I miss the occasional week of data. When I do this, the cell with the
averaging formula eg cell h30, shows a blank. All good. BUT, the graph
recognizes the result of the formula as zero not as empty, regardless that I
chose to "Plot Empty Cells as Leave Blank" in Chart Options. It has the
result of sending the plot of my series way down to zero and then back up
again. This looks bad, and ruins my trendline!
So, Excel is interpretting the result "" as zero, rather than as an empty
cell. HOw can I modify my formula or chart so that when I don't have any
data, I get an empty cell rather than "" or hte value zero?
Thanks

Hi,

You need to use NA() instead of "" in your formula.

=if(count(g23.g29)=0,NA(),average(g23.g29))

Cheers
Andy

Chas wrote:
I am plotting weekly averages on a line graph in Excel XP. I enter the weekly
numbers above a cell (g30) containing this formula:
=if(count(g23.g29)=0,"",average(g23.g29))
and plot that result against the date in cell g31. The next week the data
goes into the range h23.h29, the averaging formula is in cell h30, and the
date is in cell h31, etc.
I miss the occasional week of data. When I do this, the cell with the
averaging formula eg cell h30, shows a blank. All good. BUT, the graph
recognizes the result of the formula as zero not as empty, regardless that I
chose to "Plot Empty Cells as Leave Blank" in Chart Options. It has the
result of sending the plot of my series way down to zero and then back up
again. This looks bad, and ruins my trendline!
So, Excel is interpretting the result "" as zero, rather than as an empty
cell. HOw can I modify my formula or chart so that when I don't have any
data, I get an empty cell rather than "" or hte value zero?
Thanks

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info