Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

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
0
fb1990
Asked:
fb1990
  • 4
  • 4
  • 2
1 Solution
 
Naresh PatelTraderCommented:
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
0
 
fb1990Author Commented:
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
 
Naresh PatelTraderCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Naresh PatelTraderCommented:
=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
0
 
fb1990Author Commented:
oh.  It is an OR.  Where A = Sammy Puff OR column B =Sammy Puff
0
 
Naresh PatelTraderCommented:
sorry change I3 = 'Sammy Puff in your excel
0
 
Ejgil HedegaardCommented:
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
1
 
fb1990Author Commented:
Hi Ejgil Hedegaard

I need the percentages to be shown
0
 
Ejgil HedegaardCommented:
See sheet
Book44.xlsx
0
 
fb1990Author Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now