max value in range

[Maciej Grzywna]

Hi,

is there a way to calculate max value within one company and repeat it in
every row of that company? In other words I have this:

Company number, Var1
12, 6
12, 4
12, -3
34, 2
34, 4
56, -5
78, -4
78, -12

and would like to get this:

Company number,Var1, Var2
12, 6, 6
12, 4, 6
12, -3, 6
34, 2, 4
34, 4, 4
56, -5, -5
78, -4, -4
78, -12, -4

thanks
Maciek

 

[Bernie Deitrick]

Maciej,

Array enter (enter using Ctrl-Shift-Enter) the formula

=MAX(IF(A1=$A$1:$A$100,$B$1:$B$100))

with company numbers in A1:A100, and values in B1:B100.

HTH,
Bernie
MS Excel MVP

 

[hall.jeff]

This is either a very easy answer or a very hard one...

if your data is arranged slightly differently:

Company #, #1, #2, #3, etc.

so that the above data looks like this:
12, 6,4,-3
34,2,4
56,-5
78,-4,-12

then it's a simple max() function that you can use...

if, however, you've got to have your data arranged as presented then
you've got two choices

1) Use a Pivot Table... Pivot table will let you do the maximum of a
group... This is the best solution for finding the maximum...

2) If you need to create the exact layout you've listed, I would do
the following
a) Sort by your "company number"...
b) add this formula in column C (assuming column a and b are as listed
above)
=IF(A2=A1,C1,MAX(OFFSET(B2,0,0,COUNTIF(A:A,A2),1)))... Start this
formula in C2 and copy and paste it down
C1 gets special treatment and gets this formula
=MAX(OFFSET(B2,0,0,COUNTIF(A:A,A2),1))

This is a bit cludgy for my tastes but it may be the solution your
looking for.

 

[hall.jeff]

That works too and is a bit more elegant... just be aware that you
can't do whole columns (i.e. $A:$A returns #NUM!) so you've got to
update your range if your list gets too long

 

[Maciej Grzywna]

Sorted,
thank you guys.

Maciej

 

[Rick Rothstein \(MVP - VB\)]

That works too and is a bit more elegant... just be aware that you

can't do whole columns (i.e. $A:$A returns #NUM!) so you've got to
update your range if your list gets too long

You could always modify the formula like this...

=IF(B1="","",MAX(IF(A1=$A$1:$A$1000,$B$1:$B$1000)))

(still array-enter it with Ctrl+Alt+Enter) and then make the range large
enough to cover any possible future needs.

Rick