Charting values where a formula is caluculated

Fiona · Mar 11th, 2002, 08:02 PM

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 · Mar 11th, 2002, 08:08 PM

Quote:

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,

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 11th, 2002, 08:02 PM	#1
Fiona New Member Join Date: Mar 2002 Location: Sydney, Australia Posts: 3	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?