Excel - Finding Max Value of Column B for Each Value in Column A

[AccessUser22]

Is there a way to do the following in excel through a formula? For each value in Column A, I would like to find the maximum value in Column B. So for example:

A B
Monday 10
Tuesday 15
Monday 20
Wednesday 5
Monday 15

The formula would return

A B
Monday 20
Tuesday 15
Wednesday 5

Any ideas?

 

[SkipVought]

hi,
Enter as an ARRAY FORMULA
[tt]
=MAX((Day=A2)*(Val))
[/tt]
I am using Named ranges for the two columns
[tt]
Day Val
Monday 10
Wednesday 15
Monday 20
Wednesday 5
Monday 15
[tt]

Skip,
[sub]
[red][/red]
[/sub]

 

[feipezi]

Skip,
Please forgive me since I am kind of slow on this.
You set up 2 Named Ranges, Day and Val, then key in the Array formula at Columns("H"):

=MAX((Day=A2)*(Val))

But I got '#VALUE'. I tried to check the steps of calculation but I had nothing.
Could you elaborate a little bit more?
Thanks in advance.

 

[PHV]

Better forum for questions about formulas:
forum68

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

You have NON-NUMEIC values in the Val RANGE.

Skip,
[sub]
[red][/red]
[/sub]

 

[yogia]

Hi AccessUse22:

In addition to the solution provided by Skip, you may also try the following array formula (to be entered with CTRL+SHIFT+ENTER rather than with just ENTER) in cell E1 and then copy it down to cells E2 and E3

=MAX(IF($A$1:$A$5=D1,$B$1:$B$5))

where I have your original days in cells A1:A5, and values in cells B1:B5
[tt]
A B D E
-------------------------------------
Mon 10 Mon 20
Tue 15 Tue 15
Mon 20 Wed 5
Wed 5
Mon 15
[/tt]


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi AccessUser22:

Following up on last post, you may also use the following non-array formula in cell E1 ...

=MAX(INDEX((A1:A5=D1)*(B1:B5),0))

and then copy it down to cells E2 and E3.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com