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)+ ..
microsoft-excel worksheet-function
asked Jan 11 at 19:05
StepanStepan
15919
add a comment |
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)+ ..
microsoft-excel worksheet-function
asked Jan 11 at 19:05
StepanStepan
15919
add a comment |
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)+ ..
microsoft-excel worksheet-function
asked Jan 11 at 19:05
StepanStepan
15919
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)+ ..
microsoft-excel worksheet-function
microsoft-excel worksheet-function
asked Jan 11 at 19:05
StepanStepan
15919
asked Jan 11 at 19:05
StepanStepan
15919
asked Jan 11 at 19:05
StepanStepan
15919
asked Jan 11 at 19:05
StepanStepan
15919
asked Jan 11 at 19:05
StepanStepan
15919
15919
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
add a comment |
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).
answered Jan 13 at 15:55
StepanStepan
15919
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 inG1cell and do=C1*$G$1
– Stepan
Jan 14 at 16:13
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1393289%2fhow-to-compute-weighted-sum-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
add a comment |
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.
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
add a comment |
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.
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
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.
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
answered Jan 11 at 19:15
Scott CranerScott Craner
11.5k1815
11.5k1815
add a comment |
add a comment |
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).
answered Jan 13 at 15:55
StepanStepan
15919
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 inG1cell and do=C1*$G$1
– Stepan
Jan 14 at 16:13
add a comment |
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).
answered Jan 13 at 15:55
StepanStepan
15919
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 inG1cell and do=C1*$G$1
– Stepan
Jan 14 at 16:13
add a comment |
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).
answered Jan 13 at 15:55
StepanStepan
15919
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).
answered Jan 13 at 15:55
StepanStepan
15919
edited Jan 14 at 16:10
answered Jan 13 at 15:55
StepanStepan
15919
answered Jan 13 at 15:55
StepanStepan
15919
answered Jan 13 at 15:55
StepanStepan
15919
15919
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 inG1cell and do=C1*$G$1
– Stepan
Jan 14 at 16:13
add a comment |
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 inG1cell and do=C1*$G$1
– Stepan
Jan 14 at 16:13
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
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
@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
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1393289%2fhow-to-compute-weighted-sum-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
