excel formula question

Shanehaggerty
Shanehaggerty used Ask the Experts™
on
We are looking for a way to find the weighted average of a series of numbers relative to a percentage.  We need the average of b17:e17 relative to c8:c:12.  We have attached a screenshot of the document.  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
No attachment :)
Top Expert 2008

Commented:
Missing screenshot.

Usually doing sumproduct(range_values, range_Coefficients)/sum(values) works well for a weighted average

Thomas

Author

Commented:
a screen shot would be helpful, huh? :-p
excel.JPG
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Top Expert 2008
Commented:
You can use
=SUMPRODUCT(C14:F14,TRANSPOSE($D$8:$D$11))
in cell F17, entered as an array formula, ie copy in the cell and validate with Ctrl+Shift+Enter instead of just Enter.

The transpose and array formula is needed because your coefficients are not in the same orientation as your scores.

Thomas
Commented:

=(b17*c8+c17*c9+d17*c10+e17*c11)/c12

Open in new window

Top Expert 2008
Commented:
If you want to use dhiraj05's formula, you can drop the /C12 (since it's a division by 1) and add absolute addressing to all C8 to C11 coefficients so you can copy the formula down.

                          =(b17*$c$8+c17*$c$9+d17*$c$10+e17*$c$11)

T

Commented:
Thanks nutsch, I put divided by C12 as it is division by 100 as per the screenshot.
Top Expert 2008

Commented:
Screenshot is 100%, 100%=1.

Commented:
:)

Author

Commented:
My boss was very happy with those results and ended up with a 100 on his homework.  Thanks to you guys I have earned several brownie points :D He stated that while dhiraj05 provided very good insight, it was nutsch's solution that proved to be more complete (in his eyes) I am very ignorant when it comes to Excel and don't even understand the question that was posed, so I would like to ask you both how you would like the 500 points divvied up.

Commented:
glad that I could be of any help :).. points distribution is up to you.
Top Expert 2008

Commented:
Likewise, just close it now and you'll make me a genius.

Thomas

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial