I have a chart displaying 2 years worth of data €“ each month is a data point.
I want to automatically drop the oldest data point and add the newest when I
start to enter data for the new month. I know how to change the data range
manually to do this. Can it be done automatically? Using Excel 2003 running
under XP Pro.
--
ray

presume you numeric data is in B1:B100
use a helper column C with the following formula in C1:C24:

=INDIRECT("B"&MAX(IF(LEN($B$1:$B$100)0,ROW($B$1:$ B$100)+ROW
()-24,"")))

CTRL+SHIFT+ENTER to insert this array-formula (instead of using just
ENTER)

pls click YES if this helped



On 30 Maj, 03:59, rayteach wrote:
I have a chart displaying 2 years worth of data – each month is a data point.
I want to automatically drop the oldest data point and add the newest when I
start to enter data for the new month. I know how to change the data range
manually to do this. Can it be done automatically? Using Excel 2003 running
under XP Pro.
--
ray

....use that helper column C1:C24 as your data series...

On 30 Maj, 06:52, Jarek Kujawa wrote:
presume you numeric data is in B1:B100
use a helper column C with the following formula in C1:C24:

=INDIRECT("B"&MAX(IF(LEN($B$1:$B$100)0,ROW($B$1:$ B$100)+ROW
()-24,"")))

CTRL+SHIFT+ENTER to insert this array-formula (instead of using just
ENTER)

pls click YES if this helped

On 30 Maj, 03:59, rayteach wrote:



I have a chart displaying 2 years worth of data - each month is a data point.
I want to automatically drop the oldest data point and add the newest when I
start to enter data for the new month. I know how to change the data range
manually to do this. Can it be done automatically? Using Excel 2003 running
under XP Pro.
--
ray- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

yet another way
with months in A1:A100 and numeric data still in B1:B100

in C1 (months):
=OFFSET($B$1,MAX(IF(LEN($B$1:$B$100)0,ROW($B$1:$B $100)+ROW
()-24;""))-1,0)

in D1 (numeric data):
=OFFSET($A$1,MAX(IF(LEN($B$1:$B$100)0,ROW($B$1:$B $100)+ROW
()-24;""))-1,0)

again array-entered i.e. with CTRL+SHIFT+ENTER

HIH

On 30 Maj, 07:53, Jarek Kujawa wrote:
...use that helper column C1:C24 as your data series...

On 30 Maj, 06:52, Jarek Kujawa wrote:



presume you numeric data is in B1:B100
use a helper column C with the following formula in C1:C24:

=INDIRECT("B"&MAX(IF(LEN($B$1:$B$100)0,ROW($B$1:$ B$100)+ROW
()-24,"")))

CTRL+SHIFT+ENTER to insert this array-formula (instead of using just
ENTER)

pls click YES if this helped

On 30 Maj, 03:59, rayteach wrote:

I have a chart displaying 2 years worth of data - each month is a data point.
I want to automatically drop the oldest data point and add the newest when I
start to enter data for the new month. I know how to change the data range
manually to do this. Can it be done automatically? Using Excel 2003 running
under XP Pro.
--
ray- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Hi,

There are two common approaches to this problem, depending one what you
actually do.

A. If you are willing to delete old data and your data is laid out in a
column layout then
1. Select the range of data, say A1:B50 (includes titles) and choose Data,
List, Create List, OK.
2. When you add a new line of data on row 26 the chart will automatically
plot the new data.
3. Howerver, and here is the rub, to get rid of the extra data point from
row 2 you need to highlight those two cell A2:B2 and delete them from the
data set (press Ctrl+- (control minus).

B. Suppose you don't want to delete the history, the data earlier than that
which you want you chart to plot - you can use dynamic range names: Suppose
your data starts in A1:B1 and goes down an unknown number of rows of which
you want to plot the last 24. With column A containing the X axis lables and
column B the data.

1. Plot your chart using the entire range, lets say A1:B50.
2. Choose Insert, Name, Define and in the Names in workbook line enter X
3. On the Refert to line enter the formula:
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A$2:$A$1000),,-24)
4. On the Names in Workbook line enter Y
5. Edit the formula in the Refers to line to read: (change the first
reference from A to B)
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$A$2:$A$1000),,-24)
4. Click OK.
5. Select the series on the chart and on the formula bar change formula from
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$50,Sheet1!$B$2: $B$50,1)
to
=SERIES(Sheet1!$B$1,Sheet1!X,Sheet1!Y,1)
and press Enter.

When you press Enter the formula will change to Sheet1!X to something like
Book1!X. In this example I have assumed you may put data down to row 1000,
you can adjust this as needed.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"rayteach" wrote:

I have a chart displaying 2 years worth of data €“ each month is a data point.
I want to automatically drop the oldest data point and add the newest when I
start to enter data for the new month. I know how to change the data range
manually to do this. Can it be done automatically? Using Excel 2003 running
under XP Pro.
--
ray