Line Charts translating non-numerical entries as zero

[RobyneHud]

I have a table of weekly figures that automatically updates from a table of daily figures using the basic formula of IF(C1="","",C1).

I then have a Line Chart plotting the historical trend of these weekly figures. However, since Excel seems to translate all non-numerical entries as a zero, the line chart is fine up until the current week, but then drops to zero for future weeks (something which Management don't like to see!).

Is there any way of getting Excel to not plot non-numerical values?
Alternatively, can someone suggest a better way of doing the formula so that the Chart ignores these non-numerical values (I have tried using "NA" instead of just "" but get the same result).

Basically, I do not want to have to manually amend the chart source data each week, and do not want to have to manually update the table of weekly figures each week. I would prefer to avoid Macros as well if possible.

I am using the ancient Excel 2000 btw.

[Andy Pope]

The formula you need would look like

IF(C1="",NA(),C1)

This will only suppress the plotting of the data markers not the actually line.
The gaps between valid data points will be interpolated.