Returning the largest number based on multiple criteria

Mubz12

New Member
Joined
Feb 16, 2012
Messages
18
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
Do Something like this(Array formula, confirm with Ctrl-Shift-Enter):

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

Mubz12

New Member
Joined
Feb 16, 2012
Messages
18
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

Active Member
Joined
Sep 16, 2016
Messages
285
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

Well-known Member
Joined
Jan 20, 2014
Messages
1,421

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Control+shift+enter, not just enter:

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

Adjust to suit.
 

Mubz12

New Member
Joined
Feb 16, 2012
Messages
18

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top