Solved

# Weighted Average Based On  Category data from 3 Columns

Posted on 2016-10-27
21 Views
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
0
Question by:fb1990
• 4
• 4
• 2

LVL 8

Expert Comment

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)
``````
Cell K3
``````=(SUMPRODUCT((\$C\$2:\$C\$160=K2)*(\$D\$2:\$E\$160)))/SUMIF(\$C\$2:\$C\$160,K2,\$D\$2:\$D\$160)
``````
Cell L3
``````=(SUMPRODUCT((\$C\$2:\$C\$160=L2)*(\$D\$2:\$E\$160)))/SUMIF(\$C\$2:\$C\$160,L2,\$D\$2:\$D\$160)
``````

Thanks
0

LVL 1

Author Comment

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...
0

LVL 8

Expert Comment

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
0

LVL 8

Expert Comment

``````=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)
``````
For column J3 and copy across
0

LVL 1

Author Comment

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

LVL 8

Expert Comment

sorry change I3 = 'Sammy Puff in your excel
0

LVL 21

Expert Comment

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)))
``````

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

LVL 1

Author Comment

Hi Ejgil Hedegaard

I need the percentages to be shown
0

LVL 21

Accepted Solution

Ejgil Hedegaard earned 500 total points
See sheet
Book44.xlsx
0

LVL 1

Author Closing Comment

Thank you very much!
0

## Featured Post

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.