Prev: Present Value of equal payments of a future value
Next: IF AND question
From: Awrex on 5 May 2010 19:43
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...
From: T. Valko on 5 May 2010 20:14
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" <Awrex(a)discussions.microsoft.com> wrote in message
news:FDDE1903-0669-4BB1-9CF0-28BF75AD8408(a)microsoft.com...
> 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...


 | 
Pages: 1
Prev: Present Value of equal payments of a future value
Next: IF AND question