asked on

Weighted Average Based On Category data from 3 Columns

Hello EE,

Can someone please help me write a weighted average formula to calculate the weighted average for Sammy Puff? I need to calculate the weighted average for Online, Fax, Mail in column c and Sammy Puff in columns A and B

I have attached a sample data.

Thank you.
Book44.xlsx

Naresh Patel

use this formula for Cell J3

=(SUMPRODUCT(($C$2:$C$160=J2)*($D$2:$E$160)))/SUMIF($C$2:$C$160,J2,$D$2:$D$160)

Open in new window

Cell K3

=(SUMPRODUCT(($C$2:$C$160=K2)*($D$2:$E$160)))/SUMIF($C$2:$C$160,K2,$D$2:$D$160)

Open in new window

Cell L3

=(SUMPRODUCT(($C$2:$C$160=L2)*($D$2:$E$160)))/SUMIF($C$2:$C$160,L2,$D$2:$D$160)

Open in new window

Thanks

fb1990

ASKER

Hi itjockey,

How can i add Sammy Puff in Columns A and B to the formula. I only need the calculation if the value in column A is Sammy Puff and value in column B is Sammy Puff...

Naresh Patel

i dint find any match where column A = Sammy Puff and column B =Sammy Puff in same row ...that is why i had consider only column B

Naresh Patel

=SUMPRODUCT(($C$2:$C$160=J2)*($B$2:$B$160=$I3)*($A$2:$A$160=$I3)*$D$2:$E$160)/SUMIFS($D$2:$D$160,$A$2:$A$160,$I3,$B$2:$B$160,$I3,$C$2:$C$160,J2)

Open in new window

For column J3 and copy across

fb1990

ASKER

oh. It is an OR. Where A = Sammy Puff OR column B =Sammy Puff

Naresh Patel

sorry change I3 = 'Sammy Puff in your excel

Ejgil Hedegaard

Formula requires same name in column A and B in the row, but it has to be A or B.
To get the product sum $D$2:$E$160 must be $D$2:$D$160*$E$2:$E$160

With the names in column I, the formula in J3 will be.

=(SUMPRODUCT(($A$2:$A$160=$I3)*($C$2:$C$160=J$2)*$D$2:$D$160*$E$2:$E$160)+SUMPRODUCT(($B$2:$B$160=$I3)*($C$2:$C$160=J$2)*$D$2:$D$160*$E$2:$E$160))/(SUMPRODUCT(($A$2:$A$160=$I3)*($C$2:$C$160=J$2))+SUMPRODUCT(($B$2:$B$160=$I3)*($C$2:$C$160=J$2)))

Open in new window

Copy down and across for all names.
See sheet.
Book44.xlsx

fb1990

ASKER

Hi Ejgil Hedegaard

I need the percentages to be shown

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fb1990

ASKER

Thank you very much!