calculate max value

[aljafp]

hi
Say i have the following data in 4 columns
ColA, ColB, ColC, ColD

ABC, 12, C, 1
ABC, 15, C, 3
ABC, 17, C, 2
ABC, 12, P, 1
ABC, 15, P, 3
ABC, 17, P, 4
CDE, 13, C, 5
CDE, 15, C, 3
CDE, 17, C, 2

I wanted to create an additional column and is the max of ColD grouping
by ColA and ColC.

with the resulting data
ABC, 12, C, 1, 3
ABC, 15, C, 3, 3
ABC, 17, C, 2, 3
ABC, 12, P, 1, 4
ABC, 15, P, 3, 4
ABC, 17, P, 4, 4
CDE, 13, C, 5, 5
CDE, 15, C, 3, 5
CDE, 17, C, 2, 5

Can this be done ?

thanks

 

[Bernie Deitrick]

aljafp,

Assuming that your sample data table starts in row 1, enter this into cell E1:

=SUMPRODUCT(MAX($D$1:$D$9*($A$1:$A$9=A1)*($C$1:$C$9=C1)))

and copy down to match your data.

HTH,
Bernie
MS Excel MVP

 

[aljafp]

thanks bernie, it works.

Would you explain how this works ?

I tried breaking the formula into its components, but i couldn't figure
out how the formula works.

Max returns a column of numbers, but how does sumproduct work on an
array of 1 column ?

 

[Bernie Deitrick]

aljafp,

The range and range comparisons all return arrays, and the final three
arrays are multiplied together to get a final array, from which the MAX
value is drawn. Here's the formula again"

=SUMPRODUCT(MAX($D$1:$D$9*($A$1:$A$9=A1)*($C$1:$C$9=C1)))

both ($A$1:$A$9=A1) and ($C$1:$C$9=C1) evaluate to arrays of True and False
values (True is 1 and false is 0), which are then multiplied together to get
another array of 1's and 0's.
When that array is multiplied by the array of values in D, you get an array
of values from D where only both conditions are true (The others become
0's), and then the MAX is then determined.

The formula could have been written as an array formula (entered with
Ctrl-Shift-Enter):

=MAX($D$1:$D$9*($A$1:$A$9=A1)*($C$1:$C$9=C1))

SUMPRODUCT is just a convenient way around uisng array formulas, which
confuse many people.

HTH,
Bernie
MS Excel MVP