# Help with a Max function formula

#### Kmcbrine

##### New Member
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.

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### CraigM

##### Active Member
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
Thank you, thank you, thank you.

Works like a charm.

And they said it couldn't be done!

#### CraigM

##### Active Member
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
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

##### MrExcel MVP
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
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
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
That makes sense. I'm off to do some "Aladin's convention" research.

Thanks all for your help.

#### IML

##### MrExcel MVP
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.

Replies
3
Views
424
Replies
0
Views
1K
Replies
5
Views
289
Replies
3
Views
447
Replies
3
Views
448

1,181,785
Messages
5,932,033
Members
436,814
Latest member
Yurop

### 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?

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