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
Bookmarks