Excel 2007 : Common finder

[manofsteel]

Ok, so I will explain the attachement. I'm looking for the common answer for 2 numbers, "Common A" and "Common B" with ideal weight of 9. So we start from 9 in F4 and because it has to equal 24 because of B2, the G4 has to be 15.Then for F:F we increase by one, and inversly we decrease G:G by one.What can't occur is F value being the same as G or greater than G, so for instance F7 and G7 are both 12, so it doesn't satisfy expected results. Also, F8 is 13 and G9 is 11 so it can't happen. Then from those 3 that can happen, we have to take another number,which is 12 in A2, and make it a sum, so iif F4 is 9, then E4 is 3. in here column E:E can't be greater or equal to column G:G, so it can't be 12 9 15. I have hundred rows like that with bigger numbers like 1000 or 10000. Thanks.

MOS

[Marcol]

Trythis in F2

Please Login or Register  to view this content.

Is that what you need?

[manofsteel]

Not exactly, I attached a new attachement and the white inside the blue colors show what can be in the first stage. Then we take the other number, 79, and only 3 outcomes, that are in red are satisfactionary. The thing in orange cannot be. Obviously it will not always be 3 as the answer.

MOS

PS. The IDEAL is always the maximum, so you can't have more than ideal in the column it is in. For instance, 63....43 in this case.

[manofsteel]

Anyone????

[Marcol]

You have changed the workbook in Post #1 since I replied in Post #2, and omitted to show how your data in Row 2 is calculated in your new workbook.
This makes it hard to see what you are trying to achieve.

Try this in F2

Please Login or Register  to view this content.

Drag/Fill Down

[manofsteel]

Hi, it doesn't work, I updated the worksheet and this is what I try to accomplish. First we look at the ideal which in D2 is 9, and we make 24 out of it, so we have 9 and 15 which is fine because it is in increasing order. All possible outcomes of 24 are in O8:P16. Then we do another step but with 12, so we have "3,9" , "4,8" and so on, but again only 3 of those outcomes work which are in red, because I can't have 6,6,18, or 7,5, 19 because N can't exceed O.

On the other hand, if we have 63 in D3, then we make 85 as well, but we can't get 63 and 22, because it is not in increasing order, so we have to change it to 22 and 63 what you can see in H8 and I8.Now as we increase from 9 by one, now we decrease 63 by one, until 42,43 case, as I can't have 43:43, which is blue. And again, when taking 79, only 3 outcomes are possible as everything in orange makes G column greater than H which can't happen. Sorry for all that misleading.

MOS

[quekbc]

Hi manofsteel,

Is this correct? You want to find the number of possible outcomes in a set of numbers (say set X, set Y and set Z) such that
Numbers in Y + numbers in Z = Common B (with Y or Z starting from the Ideal number)
AND
Numbers in X + numbers in Y = Common A

So for example, in the workbook you posted, Common A 12, B 24, Ideal 9, the answer is 3 because there are 3 sets of numbers that fulfill the criteria which are 3-9-15, 4-8-16 and 5-7-17. Correct?

What does OPTIMUM in H2 represent?

[manofsteel]

So for example, in the workbook you posted, Common A 12, B 24, Ideal 9, the answer is 3 because there are 3 sets of numbers that fulfill the criteria which are 3-9-15, 4-8-16 and 5-7-17. Correct?

What does OPTIMUM in H2 represent?

Exactly, What is the Optimum is the maximum number, so for instance, 9 is lower than 60, so in cell O8, there is 9 followed by 15 in P8, which is good because it is in ascending order. However, if you have 63 which is higher than the OPTIMUM of 60, than instead of putting 63 in H8 and 22 in I22, I switch it, because if not we will get what is in TU range, but it is the longer way, because only what is between the violet color,is the same as the orange in FH but in longer way as we don't switch and ignore the Optimum which is fine with me, as long as I got the results. In addition, if it is possible I would be grateful if I could slide down the formula for many such examples and results being paste into sheet 2. Take a look in sheet 2 of what I intend to achieve.

Thanks in advance.

MOS

[quekbc]

Hey MOS, see if the attached is something that you want. I've not tested it extensively and limited the number to 2000. Can be extended if you want to.

Edit: Still wasn't quite sure what OPTIMUM should be so I left that one out.

[manofsteel]

Brilliant!!!!, Thanks, but have one more question, is it possible to change the "variables" or increase them? by increasing I mean that in here we had 2 variables " Common A" and " Common B" what about "Common C", "Common D" and so forth? In addition, can I arrange the variables as it increases? for instance, in the attachement, 3 variables, "A,B, and C" are in C7, C9, and C11 respectively.It is like doing first '67 and 98" and then adding a third variable of " 13" and all have to follow the same concept of increasing order.The red boxes represent all possibilities, so we would have 90 of them, as 6 x 15 = 90. What about if I have 13,67-98, which is K7,L7 and N7. We see that there is no possible combinations here. Generally, is it possible, and if so, can it be done as a general formula, so there isn't 2 formulas for 2 different arrangements.

Once again, thanks for superb answer.

MOS

[quekbc]

Hi manofsteel,

I don't suppose it is impossible, but my gut tells me that the moment you add in more commons, it becomes more and more complex to do so.

[manofsteel]

I understand, okay I will stay with the easy part, and would like to achieve something like what I have in the final attachment, I hope, it is self-explanatory, but if not, A37 has 1 possibility, as there is 20,28,29,31. You can't have A36 with 1, as there can't be 19,29,29,31, and all rows preceeding that.

Thanks

MOS

[quekbc]

Hi mos,

What's the purpose of the "Ideal" this time? It appears that the numbers just start from the given commons of 48 and 60. Can we make do without the Ideal?

[manofsteel]

Ok, let's ignore this " ideal". Sorry for that.

MOS

[quekbc]

Hi MOS,

Please see attached. I think it's what you want.

[manofsteel]

Well, I still have 3 answers instead of 4. In the attachement, in the orange are all the possibilities.48 consists of 2 numbers, and 60 of 2 as well so we have 4 altogether not 3. In red color there is possiblity that can't be as I can't have 19, 29,29,31, so first combinations is possible with 20, 28 and 29 31, which is in yellow.


Thanks

MOS

[quekbc]

Got it!

See attached. (Look to the far right)

[manofsteel]

Well, There should be 10 possible answers instead of 4 as it is in T6. I updated the worksheet, with colors, and each corresponds to each possible combinations, so for instance, if we have 21-27, we can make 28-32 and 29-31, so at the end we have a combinations like that " 21-27-28-32" and "21-27-29-31" Also, I would prefer for answers to be listed as it is in K17:N26.

Once again, thanks.

MOS

[quekbc]

My apologies. Simple change.

Here you go!

[manofsteel]

Perfect!, Thanks, Thanks, Thanks;-)

MOS