CODEWITHSUNDEEP
excelexcel-formulamaxarray-formulas
Suresh Jambhalkar
Suresh Jambhalkar

Reputation: 71

Finding maximum value in range containing same value

I have data:

A       B
1       100
1       300
1       200
2       100
2       500
3       100
3       300
3       200

I want to select maximumof(B) for same value in column1. Output should be:

A       B       C
1       100     300
1       300     300
1       200     300
2       100     500
2       500     500
3       100     300
3       300     300
3       200     300

I have tried: 

={Max(if(A:A=A1,B:B))}

This gives me the maximum value for 1 i.e. 300.

How can I copy this formula for other group of cells? It gives message You Can not move array values.
How can I achieve this?

Upvotes: 1

Views: 531

Answers (2)

pnuts
pnuts

Reputation: 59495

I suggest you delete ColumnC and start again - using your formula in C1. Then either drag C1 down as far as required or copy it and past it into C2:C whatever. But make sure the = is inside the curly brace.

Upvotes: 2

John Bustos
John Bustos

Reputation: 19574

There are lots of ways to accomplish this - The one I came up with used array formulas. Type this in:

=MAX((--(A:A=A1))*(B:B))

And then hit Ctrl+Shift+Enter to make it an array formula

Hope this does the trick

Upvotes: 1

Related Questions