chart data

[Emma Chapman 123]

I have a spreadsheet set up that updates the information every month over a
12 month period. The data i have for example is
Actual Profile
Jan 77723 77723
Feb 75319 67582
Mar 72047 73469
Apr 69557 81048
May 76672 88563
Jun 65068 77750
Jul 63819 79528
Aug 71079 90357
Sep 65793 85348
0 0 0
0 0 0
0 0 0

Obviously i have not received the data for the last three months yet but i
need to keep the formulas in to automatically update the information. I want
to select all of the information in this area but not show zeros on my chart.
This chart will then update every month as the data updates. Is this possible
to do. Please can someone help me.
Thanks

 

[Andy Pope]

Hi,

You need to modify your formula to use NA() instead of zero.

Cheers
Andy

 

[Emma Chapman 123]

If i use N/A it still shows N/A along the bottom axis and goes to zero. I
don't want to include these cells if null value on the chart data.

 

[Andy Pope]

The formula would be

=IF( <test> , <valid data> , NA() )

You could also use #N/A. Anything else will be treated as text and text is
plotted as zero.

If you do not want the months included on the axis then you will need to
create some named ranges and based the chart data upon those.
You find information on dynamic named ranges here,
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

 

[Emma Chapman 123]

Do i put that formula in the chart data source?

Thanks for your help on this one.

 

[David Biddulph]

If it shows N/A on the X axis, it sounds as if you are using the confusingly
named line chart instead of an XY chart.

 

[ShaneDevenshire]

Hi All,

I think we are working across purposes with the OP. The formulas everyone
is talking about, Emma, look like this =IF(R1=0,NA(),R1) The NA is a
function not text. Second, if you have functions in the X-axis columns,
don't use this, they will display as #N/A on the X-axis.

I suspect you don't want to display the months unless you have data other
than 0, is that correct? If so you may want to consider Dynamic Range Names.
Post back if this is what you want.

 

[Andy Pope]

No formula in the data source.
The formula is in the cells where you currently display chart data or zero.

Cheers
Andy