Prev: Counting Filtered Results Using Autofilter
Next: Conditional formatting with formula (multiple criteria)
From: tequila27 on 1 Apr 2010 19:12
Imagine that I've got a column of 100 numbers. First, I want to find the
maximum value (MAX function). Let's say that turns out to be in the 80th row.
Now I want to find the minimum value from the 80th through the last (100th)
row. Note that the
range of my use of the MIN function depends on where the original MAX value
came from. Any ideas on how to do this? I think what I need is a function
which can tell me which row contains the winning MAX value. Something like:

=ROW(MAX(R1C1:R100C1))

but that obviously doesn't work because the ROW functions wants a reference
- not a value.
From: Dave Peterson on 1 Apr 2010 19:51
=MIN(INDEX(A:A,MATCH(MAX(A:A),A:A,0)):A100)

(Where A100 is the last used (or after the last used cell in column A).)

tequila27 wrote:
>
> Imagine that I've got a column of 100 numbers. First, I want to find the
> maximum value (MAX function). Let's say that turns out to be in the 80th row.
> Now I want to find the minimum value from the 80th through the last (100th)
> row. Note that the
> range of my use of the MIN function depends on where the original MAX value
> came from. Any ideas on how to do this? I think what I need is a function
> which can tell me which row contains the winning MAX value. Something like:
>
> =ROW(MAX(R1C1:R100C1))
>
> but that obviously doesn't work because the ROW functions wants a reference
> - not a value.

--

Dave Peterson
 | 
Pages: 1
Prev: Counting Filtered Results Using Autofilter
Next: Conditional formatting with formula (multiple criteria)