MS Excel 2007: Calculate a weighted value based on number of months
Question: In Microsoft Excel 2007, I have scores for either 3 months or 6 months. I would like to calculate a weighted value for each month, with the first month weighing the most and the last month weighing the least.
My weight factors are as follows:
| Weight factor (3 months of data) |
Weight factor (6 months of data) |
|
|---|---|---|
| Month 1 | 1.5 | 2 |
| Month 2 | 1.0 | 1.5 |
| Month 3 | 0.5 | 1 |
| Month 4 | not applicable | 0.75 |
| Month 5 | not applicable | 0.5 |
| Month 6 | not applicable | 0.25 |
What would the formulas be for each of the months?
Answer: In the spreadsheet below, the cells with the orange background color are where we need to place the formulas:
To calculate the weighted value for Month 1 in column C, you would place the following formula in cell C8 that uses the IF function:
=B8*(1/O8)*(IF(O8=3,1.5,IF(O8=6,2,0)))
To calculate the weighted value for Month 2 in column E, you would place the following formula in cell E8:
=D8*(1/O8)*(IF(O8=3,1,IF(O8=6,1.5,0)))
To calculate the weighted value for Month 3 in column G, you would place the following formula in cell G8:
=F8*(1/O8)*(IF(O8=3,0.5,IF(O8=6,1,0)))
To calculate the weighted value for Month 4 in column I, you would place the following formula in cell I8:
=H8*(1/O8)*(IF(O8=6,0.75,0))
To calculate the weighted value for Month 5 in column K, you would place the following formula in cell K8:
=J8*(1/O8)*(IF(O8=6,0.5,0))
To calculate the weighted value for Month 6 in column M, you would place the following formula in cell M8:
=L8*(1/O8)*(IF(O8=6,0.25,0))
Advertisements