Formula to find the minimum value with constraint

[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 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.

[:) Sixthsense :)]

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

[wcymiss]

Just for the data in Attached Files：

Please Login or Register  to view this content.

Enter this array formula with Ctrl+Shift+Enter.

this formula is too long,see #7

[Fotis1991]

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

[LBala]

No, there is no duplicate values in the same column. There are no duplicate values in the same row either.
Thanks.

[LBala]

Sorry about that. I just changed the title.
Thanks.

[wcymiss]

Hi, LBala

I have modified my formula like this:

Please Login or Register  to view this content.

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

[XOR LX]

That's simply incredible, wcymiss.

[wcymiss]

That's simply incredible, wcymiss.

Thank you,XORLX
Your praise is my best to encourage

[LBala]

Your solution is mind boggling wcymiss, I honestly thought no one would come up with a single formula for that. The formula really startled me and I am still trying to understand how it works!

If the table has 6 columns and 6 rows, how would the formula be? I tried changing it to apply to a 6x6 table but could not...

Also, what I am missing is where to locate the selected values on the table, any suggestion on how to do it?

Thanks for the reply. Your solution for the problem really caused an impression on me...

[:) Sixthsense :)]

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

[wcymiss]

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.

[LBala]

Hi wcymiss;

Your english is good, I understand perfectly what you're writing.
The above formula is not working on a 6x6 table, it returns a 0 result... Have you tested it?

By the way, how did you come up with the "=6" in the formula? What's the idea behind it?
Thanks for taking up this challenge.

[XOR LX]

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

Regards

[XOR LX]

Sadly, also cross-posted:

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

[Fotis1991]

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).

[LBala]

21 inside the formula for the 6x6 table works.

[wcymiss]

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:

Please Login or Register  to view this content.

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.

[LBala]

Thanks for the help once again wcymiss, you are fantastic!

[icestationzbra]

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:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

to wcymiss, i tip my hat...