Returning Descending Values
 

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

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()

"Jim" wrote:

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

Find the nth largest value by way of

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



"Jim" wrote:

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

Jim,

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

=LARGE(P4:P65536,2)


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



"Jim" wrote in message
...
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

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!

In article ,
Jim wrote:

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

"Domenic" wrote in message
...
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

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.

"Duke Carey" wrote:

Find the nth largest value by way of

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



"Jim" wrote:

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

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.

"bj" wrote:

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()

"Jim" wrote:

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

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.

"Domenic" wrote:

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!

In article ,
Jim wrote:

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

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.

"Chip Pearson" wrote:

Jim,

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

=LARGE(P4:P65536,2)


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



"Jim" wrote in message
...
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

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.

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))

On Mon, 26 Sep 2005 09:40:04 -0700, Jim wrote:

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.

"Domenic" wrote:

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!

In article ,
Jim wrote:

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

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

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

"Jim" wrote:

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.

"bj" wrote:

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()

"Jim" wrote:

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

bj wrote:
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?

"Aladin Akyurek" wrote:


bj wrote:
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)