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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS

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,210
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
 

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
good stuff, glad you got it sorted.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,863
Messages
5,834,051
Members
430,259
Latest member
msthiagu

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