# How to compute weighted sum in excel?

I have a table below and want to compute a sum of products of a value of an element multiplied by a square of a distance to that element.

For example, for F(1) it would be sum( SUMSQ(A1-A(i),B1-B(i)) * c(i))

I've tried tried sum and sumproduct but couldn't figure out how to build a formula How to do it in excel?

`23/Max(sqrt((1-1)^2+(2-2)^2),1) + (-29)/Max(sqrt((1-3)^2+(2-3)^2),1) +34/Max(sqrt((1-8)^2+(2-7)^2),1) +42/Max(sqrt((1-9)^2+(2-9)^2),1)+ ..`

Using SUM as an array formula:

``````=SUM(C2/MAX(SQRT((A2-\$A\$2:\$A\$5)^2+(B2-\$B\$2:\$B\$5)^2),1))
``````

Being an array put the formula in the formula bar of D2, Hit Ctrl-Shift-Enter instead of Enter to exit edit mode, then copy the formula down.

Scott's answer explains the general approach. I've ended up with this formula:

`=SUMPRODUCT( (\$P\$1+ ( INDEX(\$J\$4:\$J\$685,N(IF(1,ROW(INDIRECT("1:682"))))) - \$J4)^2+( INDEX(\$K\$4:\$K\$685,N(IF(1,ROW(INDIRECT("1:682"))))) - \$K4)^2)^(-1/2), INDEX(\$M\$4:\$M\$685,N(IF(1,ROW(INDIRECT("1:682"))))))`

Update. I've tested Scott's answer. It is incorrect. Question is asking for a sum of impacts of neighbors corrected for their distance. Scott answers how to compute reciprocal sum of distances multiplied y the weight of this element.

Question asks: For Nth element it sums Value(i)/Distance(N,i)
Scott's formula: For Nth element sum Value(N)/Distance(N,i)

Note italic bold i vs N. Hence this answer is correct. It is much more complicated because for each row I need a pointer get a pair of values from each other row (i).

• Why? And how do your cell and range references relate to anything in the question? What is the added value of this convoluted approach (why would anyone use this instead of Scott's solution)? – fixer1234 Jan 14 at 0:02
• @fixer1234 Thank you for brinning this up. Please see the update to my answer. The question didn't change. Note the i in `(SUMSQ(A1-A(i),B1-B(i)) * c(i)`. Scott's answer replaced c(i) with c(1) making it a trivial problem. In fact, he can compute that sum once, place in `G1` cell and do `=C1*\$G\$1` – Stepan Jan 14 at 16:13