Help needed on finding a formula for this...

[LBala]

I have a simple table with A, B, and C columns and D, E, F rows and numeric values in each cell of the table. I need to get one value from each column (only one value per column) that will result in the lowest possible number when adding those values up. There is one constraint: I can not get cells that belong to the same row.

On the example I am providing, the sum of the values 404, 970 and 604 totals 1978 which is the lowest possible result on that table provided that I'm selecting one cell per column and each selected cell belongs to an unique row.

Is there an excel formula for this problem? I’m looking for a formula to provide what the lowest possible number is and also need to identify the cells being selected.

Thanks in advance.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]984[/TD]
[TD]970[/TD]
[TD]1048[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]404[/TD]
[TD]1454[/TD]
[TD]396[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]663[/TD]
[TD]1797[/TD]
[TD]604[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

2 posts removed for consideration by the Moderators. In the meantime ..

@XOR LX
Please be aware of #1 and #16 of the Forum Rules
In general, pointing out that it is cross-posted, providing a link to the cross-post and mentioning forum rule #10 should be sufficient. If anything further is required, refer rule #16

@LBala
Cross-posting is not against our rules. However, for the future, you might note #10 of the Forum Rules as well as rules #1 and #16

Let's keep the forum friendly.

 

[LBala]

A member in another forum came up with an very impressive formula for this problem. I would like to give him credit for that and would like to provide the link for the answer here so everybody can see his formula. How should I proceed if that is Ok to do so? I'm a new member and want to comply with the rules.

 

[shg]

The UDF provided achieves everything I'm looking for but the problem is that I have multiple tables with multiple formats (from 3x3 tables up to 50x50) to work with, ...

There's a UDF at http://www.mrexcel.com/forum/excel-questions/769312-find-minimum-matrix.html that will work for arbitrary-size matrices, but it would take several lifetimes to do a 30 x 30.

 

[LBala]

Your UDF works but I can't get the location of each selected cell. I am having the same issue the guy you were corresponding with has.

 

[shg]

See http://www.mrexcel.com/forum/excel-questions/769312-find-minimum-matrix.html#post3770816