Progressive chart

  • Thread starter Thread starter Marius
  • Start date Start date
M

Marius

Hello,

I have to create a chart to follow-up some data among the entire year.
Obviously the values for July-Dec (for eg) are not available for the moment.
Therefore the value for July-Dec are 0.

A line chart will show all the values, the ones >0 from January until June,
and 0 for the rest of them. I need my chart to stop in June. I tried to put
<blank> or N/A for the months to come but the chart still shows 0.

Do you have any suggestion?
 
You need to either make is display the #N/A error [input =NA() don't just
type N/A] or you can use dynamic charting.

To do this, we need to setup a named
range that defines your x-axis (months to use).
Not sure how your data is setup, but you need to create a formula that
counts how many months you want to use (for now, lets say the result is 7).

If your x-axis data starts in A2 and goes down, go to Insert-Name-Define.
Give the range a memorable name (MyRange), and input this formula:
=OFFSET($A$2,0,0,7,1)
Click "add"
(note that the 7 is where you would either have a cell reference, or formula
calculating how many months you need.)
If your data runs horizontal starting in B1, change formula to:
=OFFSET($B$1,0,0,1,7)

Now, on your chart, go to Source Data, and for the x-axis, input this
='MyWorkbookName.xls'!MyRange

Now, as the area you defined in the named range changes in size, so too will
your graph.
 
Hi,

the best way to handle this is by laying your data out vertically. The
create your chart and apply a filter to the Date column of the data. Charts
only plot visible cells by default.
 
Back
Top