Returning Descending Values

[Guest]

Returning Descending Values

I currently use the following to find the max number is a column:
=MAX($P$4:$P$65536). If I wanted to find the second highest, then the third
highest, etc.. how would this be written?

Thanks for the help

 

[Guest]

Try the Large() function
=large($P$4:$P$65536,1)
is the same as max()
=large($P$4:$P$65536,2)
is the second largest

Small() has a similar comparison to min()

 

[Guest]

Find the nth largest value by way of

=LARGE($P$4:$P$65536,n)

 

[Chip Pearson]

Jim,

See the LARGE function. For example, to return the second largest
number, use

=LARGE(P465536,2)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Domenic]

For the second highest...

=LARGE($P$4:$P$65536,2)

For the third highest...

=LARGE($P$4:$P$65536,3)

....and so on.

Hope this helps!

 

[Bruno Campanini]

For the second highest...

=LARGE($P$4:$P$65536,2)

For the third highest...

=LARGE($P$4:$P$65536,3)

...and so on.

Just for joking:
LARGE(Range, k) = SMALL(Range, n-k+1)
SMALL(Range, k) = LARGE(Range, n-k+1)

Bruno

 

[Guest]

Great answer to my questions. Thank you. One more follow up. How do I get
the nth value to change when I am dragging down the cells? The nth is
remaining the same.

 

[Guest]

Great answer to my questions. Thank you. One more follow up. How do I get
the number value to change when I am dragging down the cells? The 1 is
remaining the same.

 

[Guest]

Great answer to my questions. Thank you. One more follow up. How do I get
the number value to change when I am dragging down the cells? The 1 is
remaining the same.

 

[Guest]

Great answer to my questions. Thank you. One more follow up. How do I get
the number value to change when I am dragging down the cells? The 1 is
remaining the same.

 

[Harlan Grove]

Jim wrote...

Great answer to my questions. Thank you. One more follow up. How do I get
the nth value to change when I am dragging down the cells? The nth is
remaining the same.

....

If you want all values in some range (which I'll denote rng) in
descending order, it's more efficient to *avoid* using the LARGE
function. If the largest value were to appear in cell E1 with the next
largest in E2, etc., try the following if you don't want duplicates.

E1:
=MAX(rng)

E2 [array formula]:
=MAX(IF(rng<E1,rng,-1E+300))

Fill E2 down as far as needed. If you want duplicates, then maybe LARGE
would be the function to use.

E1:
=LARGE(rng,ROWS(E$1:E1))

Fill E1 down as far as needed.

 

[Myrna Larson]

Instead of a literal as the 2nd argument, use a formula based on the row
number. e.g. if the first formula is in Q8,

=LARGE($P$4:$P$65536,ROW()-ROW($Q$8)+1)

or

=LARGE($P$4:$P$65536,ROWS($Q$8:$Q8))

 

[Guest]

If you start out in row 3 (for example) change it to

=large($P$4:$P$65536,row()-2)

 

[Aladin Akyurek]

If you start out in row 3 (for example) change it to

=large($P$4:$P$65536,row()-2)

[...]

What is going to happen if some rows are inserted before the formula row?

 

[Guest]

If you start out in row 3 (for example) change it to

=large($P$4:$P$65536,row()-2)

[...]

What is going to happen if some rows are inserted before the formula row?

The equation would have to be modified
one way to do it would be to name a range equal to the first equation cell
[initr = $C$3]
change the equation to
=large($P$4:$P$65536,row()-row(initr)+1)