Getting the 2nd largest or smallest valuesin a range
 

Hi there

Th MAX & MIN functions return the largest & the smallest values in a
range.

Is there a way to get the 2nd largest and smallest values?

Any help appreciated.

Thanks

Michael

One way:

=LARGE(rng,2)

=SMALL(rng,2)

In article ,
Michael Rekas wrote:

Hi there

Th MAX & MIN functions return the largest & the smallest values in a
range.

Is there a way to get the 2nd largest and smallest values?

Any help appreciated.

Thanks

Michael

Thanks

I have combined with countif "=0"+1 to ignore zero values in the
range.

Much appreciated

Michael



On Fri, 28 Jan 2005 19:26:24 -0700, JE McGimpsey
wrote:

One way:

=LARGE(rng,2)

=SMALL(rng,2)

In article ,
Michael Rekas wrote:

Hi there

Th MAX & MIN functions return the largest & the smallest values in a
range.

Is there a way to get the 2nd largest and smallest values?

Any help appreciated.

Thanks

Michael

=SMALL(IF(A1:A100,A1:A100),2) array entered using CTRL+SHIFT+ENTER will
ignore 0s and blanks

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Michael Rekas" wrote in message
...
Thanks

I have combined with countif "=0"+1 to ignore zero values in the
range.

Much appreciated

Michael



On Fri, 28 Jan 2005 19:26:24 -0700, JE McGimpsey
wrote:

One way:

=LARGE(rng,2)

=SMALL(rng,2)

In article ,
Michael Rekas wrote:

Hi there

Th MAX & MIN functions return the largest & the smallest values in a
range.

Is there a way to get the 2nd largest and smallest values?

Any help appreciated.

Thanks

Michael

Thanks Ken

I like it!

Regards

Michael

On Sat, 29 Jan 2005 15:14:22 -0000, "Ken Wright"
wrote:

=SMALL(IF(A1:A100,A1:A100),2) array entered using CTRL+SHIFT+ENTER will
ignore 0s and blanks

You're welcome :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Michael Rekas" wrote in message
...
Thanks Ken

I like it!

Regards

Michael

On Sat, 29 Jan 2005 15:14:22 -0000, "Ken Wright"
wrote:

=SMALL(IF(A1:A100,A1:A100),2) array entered using CTRL+SHIFT+ENTER will
ignore 0s and blanks