# excel formula question

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® 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

Commented:
a screen shot would be helpful, huh? :-p
excel.JPG
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
``````
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:
:)

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