Forum Discussion
Solved
how would i use sumproduct instead of average to get a weighted average in the following.
How do I change this formula to have it perform weighted average? weights are based on the oldest data having less weight than the more recent data.
(AVERAGE(D7746:INDIRECT("D"&TEXT(ROW(D7746)-$T$2,"0"))) where $T$2 is the number of rows, 5 or 10 or 15 , etc.
Thanks for any replies.
To calculate a weighted average using the SUMPRODUCT function in Excel, you can modify your formula as follows:
Assuming you have the data range in column D and the weights in column E (with the oldest data at the top and the most recent at the bottom), you can use the following formula to calculate the weighted average based on the number of rows specified in cell $T$2:
=SUMPRODUCT(D7746:INDIRECT("D"&TEXT(ROW(D7746)-$T$2,"0")), E7746:INDIRECT("E"&TEXT(ROW(E7746)-$T$2,"0"))) / SUM(E7746:INDIRECT("E"&TEXT(ROW(E7746)-$T$2,"0")))
This formula will give you a weighted average, with more recent data having a higher weight as specified in column E.
or alternative approach with LET function:
=LET(
dataRange, D7746:INDIRECT("D"&TEXT(ROW(D7746)-$T$2,"0")),
weightRange, E7746:INDIRECT("E"&TEXT(ROW(E7746)-$T$2,"0")),
weightedSum, SUMPRODUCT(dataRange, weightRange),
totalWeight, SUM(weightRange),
weightedSum / totalWeight
)The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
To calculate a weighted average using the SUMPRODUCT function in Excel, you can modify your formula as follows:
Assuming you have the data range in column D and the weights in column E (with the oldest data at the top and the most recent at the bottom), you can use the following formula to calculate the weighted average based on the number of rows specified in cell $T$2:
=SUMPRODUCT(D7746:INDIRECT("D"&TEXT(ROW(D7746)-$T$2,"0")), E7746:INDIRECT("E"&TEXT(ROW(E7746)-$T$2,"0"))) / SUM(E7746:INDIRECT("E"&TEXT(ROW(E7746)-$T$2,"0")))
This formula will give you a weighted average, with more recent data having a higher weight as specified in column E.
or alternative approach with LET function:
=LET(
dataRange, D7746:INDIRECT("D"&TEXT(ROW(D7746)-$T$2,"0")),
weightRange, E7746:INDIRECT("E"&TEXT(ROW(E7746)-$T$2,"0")),
weightedSum, SUMPRODUCT(dataRange, weightRange),
totalWeight, SUM(weightRange),
weightedSum / totalWeight
)The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
