Formula to find the minimum value with constraint

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 cells highlighted in green 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 looking for a formula to provide what the lowest possible number is and also need to identify the cells being selected.

Thanks in advance.

Whether any two duplicate values can be present in a column?

Just for the data in Attached Files：

Formula:

=MIN(IF(MMULT(IFERROR(FIND({1,2,3},ROW(123:321)),0),{1;1;1})=6,MMULT(LOOKUP(MID(ROW(123:321),COLUMN(A:C),1)+{0,3,6},ROW(1:9),N(OFFSET(B3,MOD(ROW(3:11),3),INT(ROW(3:11)/3)))),{1;1;1})))

Enter this array formula with Ctrl+Shift+Enter.

this formula is too long,see #7

wcymiss


Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification. Do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc

Hi, LBala

I have modified my formula like this:

Formula:

=MIN(IF(MMULT(FIND({1,2,3},ROW(123:321)&123),{1;1;1})=6,MMULT(N(OFFSET(B2,{1,2,3},MID(ROW(123:321),{1,2,3},1))),{1;1;1})))

Do not forget press Ctrl+Shift+Enter when you enter the array formula.

Hi wcymiss,

Handled it so nicely with indepth processing of worksheet functions

I think you made a good start here and waiting to learn lot more from your posts

That's simply incredible, wcymiss.

Originally Posted by XOR LX:3650027

That's simply incredible, wcymiss.

Thank you,XORLX
Your praise is my best to encourage

Hi LBala,

If the table has 6 columns and 6 rows,the formula should like this :

=MIN(IF(MMULT(FIND({1,2,3,4,5,6},ROW(123456:654321)&123456),{1;1;1;1;1;1})=6,MMULT(N(OFFSET(B2,{1,2,3,4,5,6},MID(ROW(123456:654321),{1,2,3,4,5,6},1))),{1;1;1;1;1;1})))

and the data must be in [C3:H8]. "B2" in formula is the "above-left" cells of your data area.

Sorry,my english is poor,I dont kown how to say that,could you understand me?

------------------------------------------

Hi Sixthsense,
I'm glad to hear that,thank you for compliment.


------------------------------------------
Sorry to reply so late.
Today is Chinease Qingming Festival,a day for remembering our dead relatives.

I believe that the 6 should actually be 21 (the sum of integers from 1-6) in that new version.

Regards

Sadly, also cross-posted:

http://www.mrexcel.com/forum/excel-q...ml#post3770344

21 inside the formula for the 6x6 table works.

So....

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Sorry LBala,I make a mistake because my carelessness.

As XorLX say, the 6 should actually be 21,it is the sum of integers from 1-6.

However,this formula has a circular reference issues.If your data area is [C3:H8],
when you enter this formula in any cell of [I3:K8] you will receive a warning about the circular reference.

other,ROW(123456:654321) will take too many time,so I modifid this formula like this:

=MIN(MMULT(IF(MMULT(FIND({1,2,3,4,5,6},MMULT(MOD(INT(ROW(1:46656)/6^{0,1,2,3,4,5}),6)+1,10^{0;1;2;3;4;5})&123456),{1;1;1;1;1;1})=21,INDEX($C$3:$H$8,N(IF(1,{1,2,3,4,5,6})),N(IF(1,MOD(INT(ROW(1:46656)/6^{0,1,2,3,4,5}),6)+1))),9^9),{1;1;1;1;1;1}))

46656=6^6, and 9^9 is a larger number then the sum of all numbers in your data.

because excel has only 1048576 rows,you can only calculate the table of 7*7.if the table has 8 columns and 8 rows(8^8=16777216),you can't use this formula to get the result.I'm afraid you should get help from VBA.

this array formula is just an attempt to simplify the wonderful, albeit very complicated, formula that wcymiss has offered. for those who may not be able / willing to use that formula because of its complexity, this formula might help you in understanding what wcymiss' is doing in a much more automated fashion:

=MIN(MMULT(N(OFFSET($B$2,{1,2,3;1,3,2;2,1,3;2,3,1;3,1,2;3,2,1},{1,2,3})),{1;1;1}))

or

=MIN(MMULT(N(OFFSET($B$2,{1,2,3},{1,2,3;1,3,2;2,1,3;2,3,1;3,1,2;3,2,1})),{1;1;1}))

to wcymiss, i tip my hat...