Thread: calculate max value

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

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" <aljafp.1wjeyl_1128697577.5101@excelforum-nospam.com> wrote in message
news:aljafp.1wjeyl_1128697577.5101@excelforum-nospam.com...
>
> 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
>
>
> --
> aljafp
> ------------------------------------------------------------------------
> aljafp's Profile: http://www.excelforum.com/member.php...o&userid=27917
> View this thread: http://www.excelforum.com/showthread...hreadid=474140
>

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 ?

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


"aljafp" <aljafp.1wkcac_1128740720.4684@excelforum-nospam.com> wrote in
message news:aljafp.1wkcac_1128740720.4684@excelforum-nospam.com...
>
> 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 ?
>
>
> --
> aljafp
> ------------------------------------------------------------------------
> aljafp's Profile:
> http://www.excelforum.com/member.php...o&userid=27917
> View this thread: http://www.excelforum.com/showthread...hreadid=474140
>