Data Series question

[Chuck M]

Hi,

I have a line chart with 2 data series. One for actual balance and one for
budgeted balance. The y-axis is $ amount, the x-axis is month. There are 12
points on the x-axis. (Jan to Dec). I have 12 data points for the budgeted
balance. I'm using a dynamically named range for the # of data points for the
actual balance series based on the current accounting month. (Jan shows 1
point, Feb shows 2 points etc.)

I'm trying to add a 3rd series for Projected balance which would start where
the actual balance stops. I have another dynamically names range as the data
source. I have already calculated figures for the Projected balance.

My problem is that it correctly plots the May $ amount but it plots in
January on the x-axis. Is there an offset somewhere that will allow me to
move the starting point for the Projected series line?

 

[Del Cotter]

I have a line chart with 2 data series. One for actual balance and one for
budgeted balance. The y-axis is $ amount, the x-axis is month. There are 12
points on the x-axis. (Jan to Dec). I have 12 data points for the budgeted
balance. I'm using a dynamically named range for the # of data points for the
actual balance series based on the current accounting month. (Jan shows 1
point, Feb shows 2 points etc.)

I'm trying to add a 3rd series for Projected balance which would start where
the actual balance stops. I have another dynamically names range as the data
source. I have already calculated figures for the Projected balance.

My problem is that it correctly plots the May $ amount but it plots in
January on the x-axis. Is there an offset somewhere that will allow me to
move the starting point for the Projected series line?

It sounds like you have a Line chart style, but you're expecting the
series to behave like an XY (Scatter) chart style.

Line charts all share the same X axis range, and the nth value of the
series always lines up with the nth value of the single X range. This is
true even with the "Time-scale" axis type.

If you want to stick with the Line chart style, then make your third
series have as many spaces in the front as necessary so that the right
value for the series has the right X-value.

If you want to keep the series as it is, then change the style of the
chart to XY (Scatter), and ensure that the months are numeric values,
e.g. 1-12. Then each series can have its own X-range that works with
that series.

 

[Shane Devenshire]

Hi,

Since you are using dynamic range names then here is what you can do:

1. Set up a dynamic range name formula like:

=IF(MATCH(E2,C5:C16,0)+ROW(C4)<ROW(C5:C16),1,0)*E5:E16

In this example the Months are listed in the range C5:C16, the current month
is in E2, and the ROW(C4) just adjust for the fact that the match start on
row 5. You could modify to eliminate that. The range E5:E16 contains the
Projected values.

With this the Projected column can contain values in all the cells or just
in the cells after the current month. Also, depending on how you are
treating the current month you may want to change the < to <=.

Cheers,
Shane Devenshire