Progressive chart

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?
 
L

Luke M

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.
 
S

Shane Devenshire

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top