Charting values where a formula is caluculated

[Fiona]

I am charting the average of my students' fluency progress over 10 weeks. In the spreadsheet I have set up, there is a formula set up to calculate the class average in their weekly test. This is then linked to a chart which updates automatically. However, for weeks where there is no data yet (for example, we are in week 6, so weeks 7-10 have no results yet) the cell where the formula for the average is displays #DIV?0! and this value is plotted as zero on the chart. Is there any way I can leave the formula in the cell but not have the chart plot this value as zero (or anything) until there are values to be averaged?

 

[Barrie Davidson]

On 2002-03-11 19:02, Fiona wrote:
I am charting the average of my students' fluency progress over 10 weeks. In the spreadsheet I have set up, there is a formula set up to calculate the class average in their weekly test. This is then linked to a chart which updates automatically. However, for weeks where there is no data yet (for example, we are in week 6, so weeks 7-10 have no results yet) the cell where the formula for the average is displays #DIV?0! and this value is plotted as zero on the chart. Is there any way I can leave the formula in the cell but not have the chart plot this value as zero (or anything) until there are values to be averaged?

Yes, assuming your formula is A1/A2, try this:

=IF(A2=0,NA(),A1/A2)

Excel will not chart the NA() value.

Regards,