weighted formula

[Guest]

How do I apply predefined weighting to a simple "average" formula in Excel?

For simplicity sake, I have 2 values that I would like to develop a weighted
average for... let's say 80% and 20% respectively. How do I write the formula
to account for those weighting values?

 

[Guest]

With your values in A1 and A2, use:

=A1*80%+A2*20%

If you have a big range of values, say in A1:A20, and their corresponding
weights in say, B1:B20, then use:

=SUMPRODUCT(A1:A20,B1:B20)

Just make sure your weights in B1:B20 add up to 1. I would use:

=IF(SUM(B1:B20)<>1,"Error!",SUMPRODUCT(A1:A20,B1:B20))

if I found myself changing weights frequently in B1:B20.

HTH
Jason Morin
Atlanta, GA

 

[Guest]

= SUMPRODUCT(A1:A2, B1:B2) / SUM(B1:B6)

Where A1 to A2 is the are the numbers of products and B1 B2 are the 80/20
weightings