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.

enter image description here

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:


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.

enter image description here


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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.