dynamic range / offset

[Jeff]

Guys,
I have a spreadsheet with timestamps on col A, then Col B contains the
values correspond to timestamps in ColA (Col C thru Col M are similar to Col
B as well). I am trying to build a chart in function of time (meaning
Values for the X axis would be B2..M2, and Y axis would be B3..M3 for the 1
series, then B3..M3 for the values in x-axis, and B4..M4 for Y axis, so
on...).
Number of rows is subject to change (time stamps), and I am only
interested for the last 21 entries (if there is less than 21 entries, plot
them all, otherwise, only plot the last 21 entries).
Can someone please help me with the equation (offset?) in name definition?

Thanks

[Biff]

Hi!

This formula works as a worksheet formula. Whether it
works as a source for a chart, I don't know but you can
give it a try:

=IF(COUNTA(A:A)>21,OFFSET(A1,COUNTA(A:A)-1,0,-21,1),OFFSET
(A1,0,0,COUNTA(A:A),1))

As a worksheet formula this would be entered as an array.
Don't do too much (hardly any) charting but I do a lot of
formula creation.

Biff

>-----Original Message-----
>Guys,
> I have a spreadsheet with timestamps on col A, then
Col B contains the
>values correspond to timestamps in ColA (Col C thru Col M
are similar to Col
>B as well). I am trying to build a chart in function of
time (meaning
>Values for the X axis would be B2..M2, and Y axis would
be B3..M3 for the 1
>series, then B3..M3 for the values in x-axis, and B4..M4
for Y axis, so
>on...).
> Number of rows is subject to change (time stamps), and
I am only
>interested for the last 21 entries (if there is less than
21 entries, plot
>them all, otherwise, only plot the last 21 entries).
> Can someone please help me with the equation (offset?)
in name definition?
>
>Thanks
>.
>

[Jeff]

Thanks for trying Biff, but the formula contains error.

"Biff" wrote:

> Hi!
>
> This formula works as a worksheet formula. Whether it
> works as a source for a chart, I don't know but you can
> give it a try:
>
> =IF(COUNTA(A:A)>21,OFFSET(A1,COUNTA(A:A)-1,0,-21,1),OFFSET
> (A1,0,0,COUNTA(A:A),1))
>
> As a worksheet formula this would be entered as an array.
> Don't do too much (hardly any) charting but I do a lot of
> formula creation.
>
> Biff
>
> >-----Original Message-----
> >Guys,
> > I have a spreadsheet with timestamps on col A, then
> Col B contains the
> >values correspond to timestamps in ColA (Col C thru Col M
> are similar to Col
> >B as well). I am trying to build a chart in function of
> time (meaning
> >Values for the X axis would be B2..M2, and Y axis would
> be B3..M3 for the 1
> >series, then B3..M3 for the values in x-axis, and B4..M4
> for Y axis, so
> >on...).
> > Number of rows is subject to change (time stamps), and
> I am only
> >interested for the last 21 entries (if there is less than
> 21 entries, plot
> >them all, otherwise, only plot the last 21 entries).
> > Can someone please help me with the equation (offset?)
> in name definition?
> >
> >Thanks
> >.
> >
>

[AlfD]

Hi!

Have a look at Stephen Bullen's pages, in particular

http://www.bmsltd.ie/Excel/Default.htm

He has a downloadable graphing example here which does more than you need but which you will probably find will take you to your solution. It's called Funcht7.

Most importantly, he explains what is going on.

Alf