Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does this confuse me?? -- ? on Offset function
I'm trying to understand how/why this works...
=OFFSET(A12,MATCH(MAX(E12:E300),E12:E300,0)-1,) So the reference cell is a12 - got that Understand the MATCH/MAX functions. I'm confused about the -1 aspect of the OFFSET function. If I understand this it's referencing one row below the reference A12... however it's referencing the same row.... the formula works, though I'm confused as to why... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does this confuse me?? -- ? on Offset function
MATCH returns a number from 1 to n (or, possibly an error).
So, if you want to include the value of cell A12 in the calculation then you have to correct the MATCH offset from 1 to n to 0 to n. To do that you subtract 1 from the result of MATCH. Let's assume E12 is the max value of the range. So: MATCH(MAX(E12:E300),E12:E300,0) = 1 If you didn't use the -1 correction then you'd have: =OFFSET(A12,1,) = A13 With the -1 correction you have: =OFFSET(A12,0,) = A12 Another way to do this is with the INDEX function: =INDEX(A12:A300,MATCH(MAX(E12:E300),E12:E300,0)) -- Biff Microsoft Excel MVP "Awrex" wrote in message ... I'm trying to understand how/why this works... =OFFSET(A12,MATCH(MAX(E12:E300),E12:E300,0)-1,) So the reference cell is a12 - got that Understand the MATCH/MAX functions. I'm confused about the -1 aspect of the OFFSET function. If I understand this it's referencing one row below the reference A12... however it's referencing the same row.... the formula works, though I'm confused as to why... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) | |||
confuse on vlookup, index match | Excel Discussion (Misc queries) |