I have a formula in google sheets that requires me to paste it into many cells. It is pretty long and complex, and I’m wondering if there is any way to make it shorter/more efficient for google sheets by either eliminating the pasting across all of the cells using an arrayformula or by using google API query language. Here is the formula:

=$D2+SUM(ARRAYFORMULA(ARRAYFORMULA(exp(-0.000792*((($A2- FILTER($A:$A,$B:$B=$C2,$C:$C<>$B2,$A:$A<$A2))))))/sum(ARRAYFORMULA(exp(-0.000792*((($A2- FILTER($A:$A,$B:$B=$C2,$C:$C<>$B2,$A:$A<$A2)))))))*FILTER(D:D,$B:$B=$C2,$C:$C<>$B2,$A:$A<$A2))))

Basically, I’m taking a number from Col D (which becomes $D3, $D4, etc.) and adding it to the sum of an arrayformula that calculates a weighted average using distance from date as weights that filters for the numbers to sum using conditions ($A:$A,$B:$B=$C2,$C:$C<>$B2,$A:$A<$A2).

Here is a layout of the table, where Date is Column A. I am filtering where the label in “Person 2” is listed as “Person 1” ($C2=$B:$B), excluding where “Person 1” is “Person 2” ($B2<>$C:$C) and future results ($A:$A<$A2).

Date Person 1 Person 2 Points

While the function works, I need to paste it into many cells, which takes a lot of time for Google Sheets to compute. If there is any optimization possible, I would greatly appreciate it!!!

Sorry if any of this is confusing, I’ll be glad to answer any questions if needed!