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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top