Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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+3 07,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. |
#3
|
|||
|
|||
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 , "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. |
#4
|
|||
|
|||
Correction...
=MIN(OFFSET(Sheet1!B1,MAX(0,COUNTA(Sheet1!B:B)-60),0,60,1)) In article , Domenic 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! |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 , "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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with offset function | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions |