Thread: Finding the largest value in an array which does not correspond to x

Hi,

I'm trying to find the largest number which does not correspond to x which in my case equals 2958465 conditioned that the cells in H corresponds to a certain number. I tried to solve it via choosing the 2nd largest number, knowing that 2958465 would always be the largest. This however, did not work, seeing as in some cases there may be 4 or 5 of the cells with the condition that corresponds to 2958465.

My formula thus far, which works if there are one or two cells which corresponds to 2958465, is as follows:

{=IF(B39="";"";IF(MAX(IF($H$4:$H$2500=H39;$N$4:$N$2500))=2958465;LARGE(IF($H$4:$H$2500=H39;$N$4:$N$2 500);2);IF(LARGE(IF($H$4:$H$2500=H39;$N$4:$N$2500);2)=2958465;LARGE(IF($H$4:$H$2500=H39;$N$4:$N$2500 );5);MAX(IF($H$4:$H$2500=H39;$N$4:$N$2500)))))}

Seeing as this does not work in all cases, it would be great if someone could help me with a formula which simply returns the largest number in an array which does not correspond to 2958465.

Thank you in advance!

M

Hi

I think the simplest way to do this, is using a helper column.

Let's say that your values are in column H. Then in the helper column(COLUMN l, for example), put the formula:>>=IF(H1=2958465;"";H1)

So using the simple function>>>=MAX(I:I), YOU HAVE YOUR RESULT.

Hope to helps you

=LARGE(H1:H30,COUNTIF(H1:H30,g1)+1) where g1 =2958465

There is always a better way..!

Well done Martin