Returning the largest number based on multiple criteria

[Mubz12]

Hi All,

I am struggling to get something to work - I think it may be pretty simple but no matter how many different variations of MAX(IF) I try it does not seem to work.

In COLUMN U I have a range of large values and am grouping my data based on this. So for example greater than 10000 and less than 15000 would make one group.

I now want to return the highest value in COLUMN G based on the corresponding criteria specified above.

Any help would be appreciated!

Cheers.

 

[godsaaint]

Do Something like this(Array formula, confirm with Ctrl-Shift-Enter):

=LARGE(IF((U:U>10000)*(U:U<15000),U:U),1)

 

[Mubz12]

Hi,

Thanks for the swift response!

I tried the formula - and replaced the ending U:U with G:G as this the column I want the MAX of.

The formula is returning n/a# (confirmed with CTRL + SHIFT + ENTER)

Any further help would be greatly appreciated.

Thank you

 

[godsaaint]

Hey mubz, if the formula is returning an error, is because is it not finding a number greater than 10000 and less than 15000 in the specified range. If you are sure there are, make sure the numbers are not stored as text.

By the way, instead of using G:G, is better to use the real range so excel doesn't calculate the entire column, for example G1:G1000

 

[BarryL]

maybe the below entered with ctrl+shift+enter

=INDEX(G:G,LARGE(IF(U:U>10000,IF(U:U<15000,ROW(U:U))),1))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(IF(U2:U100>=10000,IF(U2:U100<=15000,G2:G100)))

Adjust to suit.

 

[Mubz12]

Hi All,

Thanks you very much for the support. All formulas worked for what I needed to do - I ended using the formula from Aladin as my preferred method.

Thanks again all

Kind Regards

 

[BarryL]

good stuff, glad you got it sorted.

 

[Aladin Akyurek]

You are welcome.