how would i use sumproduct instead of average to get a weighted average in the following.

lbeamer 

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.