I posted here yesterday when I needed to find the second maximum number
in a range =Large(). However, now I need to find the next unique
maximum number. Is there a way without having to write If statements?
My current approach is:
=IF(LARGE(D:D,2)=MAX,IF(LARGE(D:D,3)=MAX,LARGE(D:D ,4),LARGE(D:D,3)),LARGE(D:D,2))
This is just the beginning so I was wondering if there is a more
effecient way to return the second unique maximum?
Thanks in advance for your help.


--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=520105

So you want to ignore all the values that are equal to the maximum?

=MAX(IF(A1:A20<MAX(A1:A20),A1:A20))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column (if you ever
transpose your data).

Or maybe...

=IF(COUNT(A1:A20)=COUNTIF(A1:A20,MAX(A1:A20)),"not enough numbers",
MAX(IF(A1:A20<MAX(A1:A20),A1:A20)))

Just in case there isn't a second unique number.

(It's still an array formula)

kwiklearner wrote:

I posted here yesterday when I needed to find the second maximum number
in a range =Large(). However, now I need to find the next unique
maximum number. Is there a way without having to write If statements?
My current approach is:
=IF(LARGE(D:D,2)=MAX,IF(LARGE(D:D,3)=MAX,LARGE(D:D ,4),LARGE(D:D,3)),LARGE(D:D,2))
This is just the beginning so I was wondering if there is a more
effecient way to return the second unique maximum?
Thanks in advance for your help.

--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=520105

--

Dave Peterson

Adjust the range to match--but you can't use the whole column

(Ignore that portion about transposing your data.)

Dave Peterson wrote:

So you want to ignore all the values that are equal to the maximum?

=MAX(IF(A1:A20<MAX(A1:A20),A1:A20))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column (if you ever
transpose your data).

Or maybe...

=IF(COUNT(A1:A20)=COUNTIF(A1:A20,MAX(A1:A20)),"not enough numbers",
MAX(IF(A1:A20<MAX(A1:A20),A1:A20)))

Just in case there isn't a second unique number.

(It's still an array formula)

kwiklearner wrote:

I posted here yesterday when I needed to find the second maximum number
in a range =Large(). However, now I need to find the next unique
maximum number. Is there a way without having to write If statements?
My current approach is:
=IF(LARGE(D:D,2)=MAX,IF(LARGE(D:D,3)=MAX,LARGE(D:D ,4),LARGE(D:D,3)),LARGE(D:D,2))
This is just the beginning so I was wondering if there is a more
effecient way to return the second unique maximum?
Thanks in advance for your help.

--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=520105

--

Dave Peterson

--

Dave Peterson

This is fantastic... it worked! Thank you so much!

Dave Peterson Wrote:
Adjust the range to match--but you can't use the whole column

(Ignore that portion about transposing your data.)

Dave Peterson wrote:

So you want to ignore all the values that are equal to the maximum?

=MAX(IF(A1:A20<MAX(A1:A20),A1:A20))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

Adjust the range to match--but you can't use the whole column (if you
ever
transpose your data).

Or maybe...

=IF(COUNT(A1:A20)=COUNTIF(A1:A20,MAX(A1:A20)),"not enough numbers",
MAX(IF(A1:A20<MAX(A1:A20),A1:A20)))

Just in case there isn't a second unique number.

(It's still an array formula)

kwiklearner wrote:

I posted here yesterday when I needed to find the second maximum
number
in a range =Large(). However, now I need to find the next unique
maximum number. Is there a way without having to write If
statements?
My current approach is:

=IF(LARGE(D:D,2)=MAX,IF(LARGE(D:D,3)=MAX,LARGE(D:D ,4),LARGE(D:D,3)),LARGE(D:D,2))
This is just the beginning so I was wondering if there is a more
effecient way to return the second unique maximum?
Thanks in advance for your help.

--
kwiklearner

------------------------------------------------------------------------
kwiklearner's Profile:
http://www.excelforum.com/member.php...o&userid=31909
View this thread:
http://www.excelforum.com/showthread...hreadid=520105

--

Dave Peterson

--

Dave Peterson


--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=520105