Excel - function to find the highest sum in a table using each row and column only once
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I've got a table in excel with 10 rows and 10 columns.
The table contains 100 different values between 1 and 3.
I want to find the highest sum of 10 values using only 1 value from each row and 1 from each column.
Do u guys know a function that finds the highest sum? - I've tried to do i manually, but there are to many combinations!
Hope it makes sense.
Thanks in advance:)
excel-formula
asked Nov 15 '18 at 20:21
user2196134user2196134
82
|
show 9 more comments
I've got a table in excel with 10 rows and 10 columns.
The table contains 100 different values between 1 and 3.
I want to find the highest sum of 10 values using only 1 value from each row and 1 from each column.
Do u guys know a function that finds the highest sum? - I've tried to do i manually, but there are to many combinations!
Hope it makes sense.
Thanks in advance:)
excel-formula
asked Nov 15 '18 at 20:21
user2196134user2196134
82
I doubt that a formula will be able to do this.
– Scott Craner
Nov 15 '18 at 20:29
DAMN, there are only 10! (3 628 800) combinations to check...
– Forward Ed
Nov 15 '18 at 20:53
1
@ForwardEd if you have a 10x10 or 100 cells and choose one you effectively remove one column and one row, 19 cells so you are down to 9^2 then 8^2 then 7^2 cells. it is the number of cells that are available to choose not the number of columns. But either way a formula or formulas will not be able to do this.
– Scott Craner
Nov 15 '18 at 22:15
1
@ScottCraner Took some more time to think about this. Broke it down to a simpler model. 2X2 grid. There are only two possible sums, top left and bottom right and then top right and bottom left. 2! =2, 2^2*1^2=4. Interesting math problem. may look pseudo could over the weekend for my own sake. But alas not a formula option I can currently think of .
– Forward Ed
Nov 16 '18 at 7:32
1
Tried the combinations for a 3x3 table and found 6=3! combinations. Guess im looking at 10! Combinations for this one.. Should I repost with vba tag er is this not worth trying to solve in excel?
– user2196134
Nov 16 '18 at 14:13
|
show 9 more comments
I've got a table in excel with 10 rows and 10 columns.
The table contains 100 different values between 1 and 3.
I want to find the highest sum of 10 values using only 1 value from each row and 1 from each column.
Do u guys know a function that finds the highest sum? - I've tried to do i manually, but there are to many combinations!
Hope it makes sense.
Thanks in advance:)
excel-formula
asked Nov 15 '18 at 20:21
user2196134user2196134
82
I've got a table in excel with 10 rows and 10 columns.
The table contains 100 different values between 1 and 3.
I want to find the highest sum of 10 values using only 1 value from each row and 1 from each column.
Do u guys know a function that finds the highest sum? - I've tried to do i manually, but there are to many combinations!
Hope it makes sense.
Thanks in advance:)
excel-formula
excel-formula
asked Nov 15 '18 at 20:21
user2196134user2196134
82
asked Nov 15 '18 at 20:21
user2196134user2196134
82
asked Nov 15 '18 at 20:21
user2196134user2196134
82
asked Nov 15 '18 at 20:21
user2196134user2196134
82
asked Nov 15 '18 at 20:21
user2196134user2196134
82
82
I doubt that a formula will be able to do this.
– Scott Craner
Nov 15 '18 at 20:29
DAMN, there are only 10! (3 628 800) combinations to check...
– Forward Ed
Nov 15 '18 at 20:53
1
@ForwardEd if you have a 10x10 or 100 cells and choose one you effectively remove one column and one row, 19 cells so you are down to 9^2 then 8^2 then 7^2 cells. it is the number of cells that are available to choose not the number of columns. But either way a formula or formulas will not be able to do this.
– Scott Craner
Nov 15 '18 at 22:15
1
@ScottCraner Took some more time to think about this. Broke it down to a simpler model. 2X2 grid. There are only two possible sums, top left and bottom right and then top right and bottom left. 2! =2, 2^2*1^2=4. Interesting math problem. may look pseudo could over the weekend for my own sake. But alas not a formula option I can currently think of .
– Forward Ed
Nov 16 '18 at 7:32
1
Tried the combinations for a 3x3 table and found 6=3! combinations. Guess im looking at 10! Combinations for this one.. Should I repost with vba tag er is this not worth trying to solve in excel?
– user2196134
Nov 16 '18 at 14:13
|
show 9 more comments
I doubt that a formula will be able to do this.
– Scott Craner
Nov 15 '18 at 20:29
DAMN, there are only 10! (3 628 800) combinations to check...
– Forward Ed
Nov 15 '18 at 20:53
1
@ForwardEd if you have a 10x10 or 100 cells and choose one you effectively remove one column and one row, 19 cells so you are down to 9^2 then 8^2 then 7^2 cells. it is the number of cells that are available to choose not the number of columns. But either way a formula or formulas will not be able to do this.
– Scott Craner
Nov 15 '18 at 22:15
1
@ScottCraner Took some more time to think about this. Broke it down to a simpler model. 2X2 grid. There are only two possible sums, top left and bottom right and then top right and bottom left. 2! =2, 2^2*1^2=4. Interesting math problem. may look pseudo could over the weekend for my own sake. But alas not a formula option I can currently think of .
– Forward Ed
Nov 16 '18 at 7:32
1
Tried the combinations for a 3x3 table and found 6=3! combinations. Guess im looking at 10! Combinations for this one.. Should I repost with vba tag er is this not worth trying to solve in excel?
– user2196134
Nov 16 '18 at 14:13
I doubt that a formula will be able to do this.
– Scott Craner
Nov 15 '18 at 20:29
I doubt that a formula will be able to do this.
– Scott Craner
Nov 15 '18 at 20:29
DAMN, there are only 10! (3 628 800) combinations to check...
– Forward Ed
Nov 15 '18 at 20:53
DAMN, there are only 10! (3 628 800) combinations to check...
– Forward Ed
Nov 15 '18 at 20:53
1
1
@ForwardEd if you have a 10x10 or 100 cells and choose one you effectively remove one column and one row, 19 cells so you are down to 9^2 then 8^2 then 7^2 cells. it is the number of cells that are available to choose not the number of columns. But either way a formula or formulas will not be able to do this.
– Scott Craner
Nov 15 '18 at 22:15
@ForwardEd if you have a 10x10 or 100 cells and choose one you effectively remove one column and one row, 19 cells so you are down to 9^2 then 8^2 then 7^2 cells. it is the number of cells that are available to choose not the number of columns. But either way a formula or formulas will not be able to do this.
– Scott Craner
Nov 15 '18 at 22:15
1
1
@ScottCraner Took some more time to think about this. Broke it down to a simpler model. 2X2 grid. There are only two possible sums, top left and bottom right and then top right and bottom left. 2! =2, 2^2*1^2=4. Interesting math problem. may look pseudo could over the weekend for my own sake. But alas not a formula option I can currently think of .
– Forward Ed
Nov 16 '18 at 7:32
@ScottCraner Took some more time to think about this. Broke it down to a simpler model. 2X2 grid. There are only two possible sums, top left and bottom right and then top right and bottom left. 2! =2, 2^2*1^2=4. Interesting math problem. may look pseudo could over the weekend for my own sake. But alas not a formula option I can currently think of .
– Forward Ed
Nov 16 '18 at 7:32
1
1
Tried the combinations for a 3x3 table and found 6=3! combinations. Guess im looking at 10! Combinations for this one.. Should I repost with vba tag er is this not worth trying to solve in excel?
– user2196134
Nov 16 '18 at 14:13
Tried the combinations for a 3x3 table and found 6=3! combinations. Guess im looking at 10! Combinations for this one.. Should I repost with vba tag er is this not worth trying to solve in excel?
– user2196134
Nov 16 '18 at 14:13
|
show 9 more comments
1 Answer
1
active
oldest
votes
My solution builds on what I wrote in the comment, i.e. you first take the maximum value in the 10x10 array, then the maximum in the 9x9 array (excluding the row/column of the first maximum), etc. My solution tries not to do everything in one formula, but I add a few helper columns, and a bit more helper rows (it is fast and dirty, but it works and is easily audited/understandable). You always can do this on a separate worksheet which you could hide if needed.
The screenshot above goes from cell A1 till Y31.
The key formulas:
- 3.55 is the result of
=MAX(B2:K11)
- The first gray cell is
=IFNA(MATCH($M12;B2:B11;0);""), and you drag this 9 cells to the left. This tries to find a match with the max result in each column of the table; - The 10 left of the 3.55 is
=MATCH(TRUE;INDEX(ISNUMBER(P12:Y12);0);0), and gives the column number of the max value. - The 2 next to the 10 is
=INDEX(P12:Y12;N12)and gives the row number of the max value. - The 1 in cell B12 is
=IF(OR(B$1=$N12;$A12=$O12);0;1), and creates a 10x10 matrix with a row and column with zeroes where the previous max value was found. - Then you multiply this with the preceding matrix and create a new 10x10 matrix below (enter
{=B2:K11*B12:K21}array formula (ctrl+shift+enter) in B22-K31 - You then copy/paste rows 12 till 31 9 times below
- The 23.02 is the total sum
=SUM($M$12:$M$211)from all 10 maximum values and is the result you are looking for. The 10 is just a check with=COUNT($M$12:$M$211)
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53327327%2fexcel-function-to-find-the-highest-sum-in-a-table-using-each-row-and-column-on%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
My solution builds on what I wrote in the comment, i.e. you first take the maximum value in the 10x10 array, then the maximum in the 9x9 array (excluding the row/column of the first maximum), etc. My solution tries not to do everything in one formula, but I add a few helper columns, and a bit more helper rows (it is fast and dirty, but it works and is easily audited/understandable). You always can do this on a separate worksheet which you could hide if needed.
The screenshot above goes from cell A1 till Y31.
The key formulas:
- 3.55 is the result of
=MAX(B2:K11)
- The first gray cell is
=IFNA(MATCH($M12;B2:B11;0);""), and you drag this 9 cells to the left. This tries to find a match with the max result in each column of the table; - The 10 left of the 3.55 is
=MATCH(TRUE;INDEX(ISNUMBER(P12:Y12);0);0), and gives the column number of the max value. - The 2 next to the 10 is
=INDEX(P12:Y12;N12)and gives the row number of the max value. - The 1 in cell B12 is
=IF(OR(B$1=$N12;$A12=$O12);0;1), and creates a 10x10 matrix with a row and column with zeroes where the previous max value was found. - Then you multiply this with the preceding matrix and create a new 10x10 matrix below (enter
{=B2:K11*B12:K21}array formula (ctrl+shift+enter) in B22-K31 - You then copy/paste rows 12 till 31 9 times below
- The 23.02 is the total sum
=SUM($M$12:$M$211)from all 10 maximum values and is the result you are looking for. The 10 is just a check with=COUNT($M$12:$M$211)
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
add a comment |
My solution builds on what I wrote in the comment, i.e. you first take the maximum value in the 10x10 array, then the maximum in the 9x9 array (excluding the row/column of the first maximum), etc. My solution tries not to do everything in one formula, but I add a few helper columns, and a bit more helper rows (it is fast and dirty, but it works and is easily audited/understandable). You always can do this on a separate worksheet which you could hide if needed.
The screenshot above goes from cell A1 till Y31.
The key formulas:
- 3.55 is the result of
=MAX(B2:K11)
- The first gray cell is
=IFNA(MATCH($M12;B2:B11;0);""), and you drag this 9 cells to the left. This tries to find a match with the max result in each column of the table; - The 10 left of the 3.55 is
=MATCH(TRUE;INDEX(ISNUMBER(P12:Y12);0);0), and gives the column number of the max value. - The 2 next to the 10 is
=INDEX(P12:Y12;N12)and gives the row number of the max value. - The 1 in cell B12 is
=IF(OR(B$1=$N12;$A12=$O12);0;1), and creates a 10x10 matrix with a row and column with zeroes where the previous max value was found. - Then you multiply this with the preceding matrix and create a new 10x10 matrix below (enter
{=B2:K11*B12:K21}array formula (ctrl+shift+enter) in B22-K31 - You then copy/paste rows 12 till 31 9 times below
- The 23.02 is the total sum
=SUM($M$12:$M$211)from all 10 maximum values and is the result you are looking for. The 10 is just a check with=COUNT($M$12:$M$211)
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
add a comment |
My solution builds on what I wrote in the comment, i.e. you first take the maximum value in the 10x10 array, then the maximum in the 9x9 array (excluding the row/column of the first maximum), etc. My solution tries not to do everything in one formula, but I add a few helper columns, and a bit more helper rows (it is fast and dirty, but it works and is easily audited/understandable). You always can do this on a separate worksheet which you could hide if needed.
The screenshot above goes from cell A1 till Y31.
The key formulas:
- 3.55 is the result of
=MAX(B2:K11)
- The first gray cell is
=IFNA(MATCH($M12;B2:B11;0);""), and you drag this 9 cells to the left. This tries to find a match with the max result in each column of the table; - The 10 left of the 3.55 is
=MATCH(TRUE;INDEX(ISNUMBER(P12:Y12);0);0), and gives the column number of the max value. - The 2 next to the 10 is
=INDEX(P12:Y12;N12)and gives the row number of the max value. - The 1 in cell B12 is
=IF(OR(B$1=$N12;$A12=$O12);0;1), and creates a 10x10 matrix with a row and column with zeroes where the previous max value was found. - Then you multiply this with the preceding matrix and create a new 10x10 matrix below (enter
{=B2:K11*B12:K21}array formula (ctrl+shift+enter) in B22-K31 - You then copy/paste rows 12 till 31 9 times below
- The 23.02 is the total sum
=SUM($M$12:$M$211)from all 10 maximum values and is the result you are looking for. The 10 is just a check with=COUNT($M$12:$M$211)
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
My solution builds on what I wrote in the comment, i.e. you first take the maximum value in the 10x10 array, then the maximum in the 9x9 array (excluding the row/column of the first maximum), etc. My solution tries not to do everything in one formula, but I add a few helper columns, and a bit more helper rows (it is fast and dirty, but it works and is easily audited/understandable). You always can do this on a separate worksheet which you could hide if needed.
The screenshot above goes from cell A1 till Y31.
The key formulas:
- 3.55 is the result of
=MAX(B2:K11)
- The first gray cell is
=IFNA(MATCH($M12;B2:B11;0);""), and you drag this 9 cells to the left. This tries to find a match with the max result in each column of the table; - The 10 left of the 3.55 is
=MATCH(TRUE;INDEX(ISNUMBER(P12:Y12);0);0), and gives the column number of the max value. - The 2 next to the 10 is
=INDEX(P12:Y12;N12)and gives the row number of the max value. - The 1 in cell B12 is
=IF(OR(B$1=$N12;$A12=$O12);0;1), and creates a 10x10 matrix with a row and column with zeroes where the previous max value was found. - Then you multiply this with the preceding matrix and create a new 10x10 matrix below (enter
{=B2:K11*B12:K21}array formula (ctrl+shift+enter) in B22-K31 - You then copy/paste rows 12 till 31 9 times below
- The 23.02 is the total sum
=SUM($M$12:$M$211)from all 10 maximum values and is the result you are looking for. The 10 is just a check with=COUNT($M$12:$M$211)
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
answered Nov 17 '18 at 15:32
Peter K.Peter K.
768313
768313
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53327327%2fexcel-function-to-find-the-highest-sum-in-a-table-using-each-row-and-column-on%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
I doubt that a formula will be able to do this.
– Scott Craner
Nov 15 '18 at 20:29
DAMN, there are only 10! (3 628 800) combinations to check...
– Forward Ed
Nov 15 '18 at 20:53
1
@ForwardEd if you have a 10x10 or 100 cells and choose one you effectively remove one column and one row, 19 cells so you are down to 9^2 then 8^2 then 7^2 cells. it is the number of cells that are available to choose not the number of columns. But either way a formula or formulas will not be able to do this.
– Scott Craner
Nov 15 '18 at 22:15
1
@ScottCraner Took some more time to think about this. Broke it down to a simpler model. 2X2 grid. There are only two possible sums, top left and bottom right and then top right and bottom left. 2! =2, 2^2*1^2=4. Interesting math problem. may look pseudo could over the weekend for my own sake. But alas not a formula option I can currently think of .
– Forward Ed
Nov 16 '18 at 7:32
1
Tried the combinations for a 3x3 table and found 6=3! combinations. Guess im looking at 10! Combinations for this one.. Should I repost with vba tag er is this not worth trying to solve in excel?
– user2196134
Nov 16 '18 at 14:13