Help with a Max function formula

Kmcbrine

New Member
Joined
Jan 13, 2005
Messages
47
a) city b) population

Cities are listed in column a multiple times.

I'm trying to get the Max in column B, if A equals Hollywood.

I would like "Hollywood) to be one of several constants shown at the bottom of the table.

I want, if a1:a5 = c11, then max b1:b5.

I'm obviously lost as can be.

Thanks in advance!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Try this formula. It assumes that all of your numbers in column B are positive, and it must be entered as an array formula (ie hit Ctrl+Shift+Enter instead of just Enter when you type the formula in).

=MAX(IF(A1:A5=C11,B1:B5,0))
 

Kmcbrine

New Member
Joined
Jan 13, 2005
Messages
47
(y) Thank you, thank you, thank you.

Works like a charm.

And they said it couldn't be done!
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
No trouble. Just be careful with those array formulas. Whenever you alter them they must be entered with Ctrl+Shift+Enter. It might be worth considering protecting the cells if others will be using your worksheet.
 

Kmcbrine

New Member
Joined
Jan 13, 2005
Messages
47

ADVERTISEMENT

Help with a MIN function formula

=MIN(IF(C3:C52=C58,E3:E52,0))

Okay, the formula above works great for MAX { }

Why won't it work for MIN???

Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Re: Help with a MIN function formula

Kmcbrine said:
=MIN(IF(C3:C52=C58,E3:E52,0))

Okay, the formula above works great for MAX { }

Why won't it work for MIN???

Thanks

It's:

=MIN(IF($C$3:$C$52=C58,$E$3:$E$52))

which you need to confirm with control+shift+enter.
 

Kmcbrine

New Member
Joined
Jan 13, 2005
Messages
47

ADVERTISEMENT

Now I'm really confused! It works, and thank you, but isn't that the same formula with absolute values? Why would that make a difference? Shouldn't it work either way?

Thanks again for your help, I appreciate it!
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
Use aladin's convention in both sets, exluding the zero,

The problem with this:
=MIN(IF(A1:A3=C11,B1:B3,0))
assuming this data
c11 = Apple
a1:b1 apple 99
a2:b2 apple 12
a3:b3 dog 88


is that the a1 and a2 = apple so the min formula is considering 99 and 12. As a3 does not equal c11, the formula is looking to the else statement (ie 0) and returning the minimum of 99, 12 and 0. Not what you want. You'd have similiar problems with the max setup if you had negative numbers.

Simply omit the ,0 part.
 

Kmcbrine

New Member
Joined
Jan 13, 2005
Messages
47
That makes sense. I'm off to do some "Aladin's convention" research.

Thanks all for your help.
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
I just meant use
=MIN(IF($C$3:$C$52=C58,$E$3:$E$52))
or
=Max(IF($C$3:$C$52=C58,$E$3:$E$52))

over
=MIN(IF($C$3:$C$52=C58,$E$3:$E$52,0))
or
=Max(IF($C$3:$C$52=C58,$E$3:$E$52,0))


A study of "Aladin's conventions" would not be a quick undertaking, but would be time well spent.
 

Forum statistics

Threads
1,148,525
Messages
5,747,194
Members
424,068
Latest member
Salim khamis

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