Troubles with the OFFSET function

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.

This will get the last value entered in column A

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

This will get the ROW # of the last entry in Col A.

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

This will sum the last 60 entries in a column - assuming a header in row 1
and no blank cells

=SUM(OFFSET(A2,MAX(0,COUNTA(A2:A65536)-60),0,60,1))

& so will this

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


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

For the last numerical value...

=LOOKUP(9.99999999999999E+307,Sheet1!B:B)

For the minimum value for the last 60 entries, provided there are no
blanks within your range...

=MIN(OFFSET(Sheet1!B1,MAX(0,COUNTA(Sheet1!B:B))-60,0,60,1))

For the maximum and average, replace MIN() accordingly.

Hope this helps!

In article <01E84F3C-EDB2-43FC-BEE5-B1AB7B5C0BBB@microsoft.com>,
"ghostrider" <ghostrider@discussions.microsoft.com> 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.

Correction...

=MIN(OFFSET(Sheet1!B1,MAX(0,COUNTA(Sheet1!B:B)-60),0,60,1))

In article <domenic22-D9B9D0.08515701062005@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> wrote:

> For the last numerical value...
>
> =LOOKUP(9.99999999999999E+307,Sheet1!B:B)
>
> For the minimum value for the last 60 entries, provided there are no
> blanks within your range...
>
> =MIN(OFFSET(Sheet1!B1,MAX(0,COUNTA(Sheet1!B:B))-60,0,60,1))
>
> For the maximum and average, replace MIN() accordingly.
>
> Hope this helps!

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.