Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighted formula
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? -- Regards, Norm W |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighted formula
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:B 20)) if I found myself changing weights frequently in B1:B20. HTH Jason Morin Atlanta, GA "Norm" wrote: 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? -- Regards, Norm W |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
weighted formula
= 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 "Norm" wrote: 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? -- Regards, Norm W |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Moving Weighted Average formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
What is the formula for weighted average? | Excel Worksheet Functions |