MAX function with mutiple and identical hits

[Masegy]

Hi all - thanks for a great site.

I have an unresolved issue using MAX function that has multiple and identical hits.

If anyone has a tip or formula for my issue I am grateful.

My data (two columns)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer ID[/TD]
[TD]Customer age[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]62[/TD]
[/TR]
</tbody>[/TABLE]




What I want
[TABLE="width: 500"]
<tbody>[TR]
[TD]Cluster ID*[/TD]
[TD]--> Max cluster age[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]62[/TD]
[/TR]
</tbody>[/TABLE]

* = I have this

What I get when using =MAX(IF(Customer ID range=Cluster ID, Age range)) using Control+Shift+Enter

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cluster ID[/TD]
[TD]--> Max cluster age[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]66[/TD]
[/TR]
</tbody>[/TABLE]

I have automatic calculation on.

Any ideas? Thanks in advance!

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you named your ranges? if not what is the actual formula? Did you enter it in the first cell and copy it down or did you enter it in all cells at once?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Customer ID[/TD]
[TD]Customer age[/TD]
[TD][/TD]
[TD]Customer ID[/TD]
[TD]MAX Customer age[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

66​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

66​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

65​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

27​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

66​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

62​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

27​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

3​

[/TD]
[TD]

58​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

3​

[/TD]
[TD]

62​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



E2 =MAX(IF($A$2:$A$7=D2,$B$2:$B$7)) Control +Shift+ Enter Copy down

 

[W8253]

I'd use a Pivot table with the following:

Rows: Customer ID
Values: Customer age (Change this from Count to Max)

 

[Masegy]

Hi,

Thanks for all the help. I think something is wrong in my formula or Excel, but I was able to solve the issue using W8253's suggestion - genius.

Thanks!