Getting the 2nd largest or smallest valuesin a range

[Michael Rekas]

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

 

[JE McGimpsey]

One way:

=LARGE(rng,2)

=SMALL(rng,2)

 

[Michael Rekas]

Thanks

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

Much appreciated

Michael

 

[Ken Wright]

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

 

[Michael Rekas]

Thanks Ken

I like it!

Regards

Michael

 

[Ken Wright]

You're welcome