"Maxif"

[MountainHawk]

Is there any way to pick out the maximum/minimum of a column based on what is in another column, sort of like sumif does for sums?

If my data file was:

PA 35000
NJ 5000
PA 1000000
NJ 45000
SC 50000

I'd want to create a table that showed:

NJ 45000
PA 1000000
SC 50000

Thanks.

[Cho Da]

Use a pivot table.

[MountainHawk]

OK ... I was hoping their was a formula to do it, but I guess a pivottable it is

[wooHoo]

Use Ctrl+Shift+Enter (CSE formulas) to supercharge your formulas!.


http://www.mrexcel.com/tip011.shtml

[Skippy]

Expanding on louisw's suggestion, if your table is in the range A1:B5 and "NJ" is in A8 then the formula
{=MAX(IF(A8=$A$1:$A$5,B1:B5,""))} will give you the answer you're looking for. (you get the curly brackets by entering the formula using Ctrl+Shift+Enter)

[TwistedMentat]

Use Ctrl+Shift+Enter (CSE formulas) to supercharge your formulas!.
Otherwise known as array formulas. You can do a lot of amazing things with them and they are partially protected in that no one can go in and change one cell in the array. However, it might make updating the worksheet difficult.

I only recently saw use of the pivot table to accomplish this, and the simplicity is very appealing.

[Brad Gile]

Function MaxIf(ByVal X As Variant, ByVal R1 As Range, ByVal R2 As Range) As Double
'RETURNS THE MAXIMUM VALUE OF CELLS IN R2 WHEN CORRESPONDING CELL IN R1 HAS VALUE X
'R1 AND R2 ARE COLUMNAR RANGES OF THE SAME LENGTH

Dim J As Long
Dim MX As Double
MX = -1000000000000#

For J = 1 To R1.Rows.Count
If R1(J) = X Then
If R2(J) > MX Then MX = R2(J)
End If
Next J
MaxIf = MX
End Function :D

Brad