Troubles with the OFFSET function

Thanks alot Duke and Dom, everythings working great. The last thing i am now
trying to figure out is how to setup a function to keep running 1 day 5 day
and 20 day changes off the last entry (i.e. if i enter new days data on cell
a65 id like it to automatically take that newest entry and subtract a64, a60,
and a45). Any hints??

Again thanks so much for the help.

"ghostrider" wrote:

> Hey all. Here is my dilemma. On sheet 1 column B i currently have 64
> numerical entries to which i am adding 1 entry each day. On sheet 2 i am
> trying to do the following things. First i want one cell to automatically
> display the last entry i have made. Secondly, i am trying to do min max and
> averages for the last 60 entries.
>
> I know that you need to use some combination of the count function within
> the offset function but cant seem to get it right. I am uncertain of what i
> should be using for the reference, rows, columns, height width parts. Any
> suggestions???
>
> Thanks much.

Without testing, you can get the one day change with

=INDIRECT("A"&MATCH(9.99999999999999E+307,A:A))-INDIRECT("A"&MATCH(9.99999999999999E+307,A:A)-1)

Adjust the final "-1" to get the other values


"ghostrider" wrote:

> Thanks alot Duke and Dom, everythings working great. The last thing i am now
> trying to figure out is how to setup a function to keep running 1 day 5 day
> and 20 day changes off the last entry (i.e. if i enter new days data on cell
> a65 id like it to automatically take that newest entry and subtract a64, a60,
> and a45). Any hints??
>
> Again thanks so much for the help.
>
> "ghostrider" wrote:
>
> > Hey all. Here is my dilemma. On sheet 1 column B i currently have 64
> > numerical entries to which i am adding 1 entry each day. On sheet 2 i am
> > trying to do the following things. First i want one cell to automatically
> > display the last entry i have made. Secondly, i am trying to do min max and
> > averages for the last 60 entries.
> >
> > I know that you need to use some combination of the count function within
> > the offset function but cant seem to get it right. I am uncertain of what i
> > should be using for the reference, rows, columns, height width parts. Any
> > suggestions???
> >
> > Thanks much.

Here's another way...

B1:

=LOOKUP(9.99999999999999E+307,A:A)

....which gives you the last numerical value

C1: enter 1

C2: enter 5

C3: enter 20

....which represents the number of days

D1, copied down:

=$B$1-INDEX(A:A,MATCH(9.99999999999999E+307,A:A)-C1)

Hope this helps!

In article <AF22BD4E-3395-43C8-B536-FA2EB8E73517@microsoft.com>,
"ghostrider" <ghostrider@discussions.microsoft.com> wrote:

> Thanks alot Duke and Dom, everythings working great. The last thing i am now
> trying to figure out is how to setup a function to keep running 1 day 5 day
> and 20 day changes off the last entry (i.e. if i enter new days data on cell
> a65 id like it to automatically take that newest entry and subtract a64, a60,
> and a45). Any hints??
>
> Again thanks so much for the help.
>
> "ghostrider" wrote:
>
> > Hey all. Here is my dilemma. On sheet 1 column B i currently have 64
> > numerical entries to which i am adding 1 entry each day. On sheet 2 i am
> > trying to do the following things. First i want one cell to automatically
> > display the last entry i have made. Secondly, i am trying to do min max
> > and
> > averages for the last 60 entries.
> >
> > I know that you need to use some combination of the count function within
> > the offset function but cant seem to get it right. I am uncertain of what
> > i
> > should be using for the reference, rows, columns, height width parts. Any
> > suggestions???
> >
> > Thanks much.