Need professional help with combinations

[lotofspam]

hello all,

I really hope that you can help me out. I need an excel calculation list that can do the following thing:

In the column A (A2-A100) somebody types in several lenghts, eg 1969, 928, 456, 518 etc.

- Fist the program subtracts 2x175 from the lenghts in A and gives back the result in column B (B2-B100).
- Then the Program calculates which combinations of the lenghts 490, 290, 190, 90, 60 fit in to the lenghts in column B, prefering the longest to the shortest in regard to that the amount of the space that remains devided by (the number of units that was used +1) is >0 and <=100.


Eg:

A2-A100
length
1969
928
1331

B2-B100
lenght-2x175
1619
578
981

C2-C100
test 490
3
1
1

D2-D100
test 290
0
0
1

E2-E100
test 190
0
0
0

F2-F100
test 90
0
0
1

G2-G100
test 60
0
0
0

H2-H100
amount remains
149 (=1619-3x490-0x290-0x190-0x90-0x60)
88 (=578-1x490-0x290-0x190-0x90-0x60)
111 (=981-1x490-1x290-1x90-0x90-0x60)

I2-I100
devided by used parts (space between the test parts)
37,25 (=149/(1+ 3+0+0+0+0)) <---- 37,25 is >0 and <=100.
44 (=88/(1+ 1+0+0+0+0)) <---- 44 is >0 and <=100.
27,75 (=111/(1+ 1+1+0+1+0)) <---- 27,75 is >0 and <=100.



I did already an excel table that calculates the "lenght-2x175" in the beackground, the "amount remains" and the "devided by used parts". But i need to test all lenghts (490,290,190,90,60) manually if they fit. And this is where i need your help. actually i would like to only give excel the lenghts in A and he calculates everything automatically.

can you please help me out?

thank you so much in advance!

[Beamernsw]

I got different answers to you, but hopefully it'll work.

Edit: My formulas just use simple math to calculate the remaining length at each column including H.
kvsrinivasamurthy's sumproduct formula is much more elegant.

Column B simply deducted the required 2 x 175 from column A

Please Login or Register  to view this content.

Column C calculates the number of 490's that will fit in the remaining length

Please Login or Register  to view this content.

In this example of 1969mm less 2 x 175, B2/490 would equal 3.30408. The command INT removes any decimals returning just the "3".

Column D calculates the number of 290's that will fit in the new remaining length

Please Login or Register  to view this content.

Column E calculates the number of 190's that will fit in the new remaining length

Please Login or Register  to view this content.

Column F calculates the number of 90's that will fit in the new remaining length

Please Login or Register  to view this content.

Column G calculates the number of 60's that will fit in the new remaining length

Please Login or Register  to view this content.

Column H calculates the final remaining length.

Please Login or Register  to view this content.

[AliGW]

Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand.

[kvsrinivasamurthy]

Pl see file.
In B2 then copied down

Please Login or Register  to view this content.

In C2 then copied down till Column H

Please Login or Register  to view this content.

[lotofspam]

Hello and thank you for your help so far. I made some changes to the table.
Where I still need your help is the idea how to manage that the calculation guidelines are based on that the "gap output" has to be >10 and <=100. do you have an idea how to manage that?

Thank you for the help

[AliGW]

My formulas just use simple math to calculate the remaining length at each column including H.

This is not satisfactory as an explanation of your proposed solution. You should provide the formulae used and state in which cells they should go in the body of your post, please.

[Beamernsw]

I think the simplest idea would be to change your measurements in Row 2. Make B2=180, C2=500, D2=300..etc..G2=75.
Gap output formula for Cell I3 would be:-

Please Login or Register  to view this content.

If you need your original numbers shown in the cells instead of changing them as I described, then insert a new Row between Row 1 and Row 2. Then copy the cells from the old Row 2 to into the new Row 2. Then change the numbers in the new Row 3 (old Row 2) to the ones I suggested and then hide that row.

[lotofspam]

hello and thank you for the help, but unforunately this doesnt solve the problem.

I input a lenght, eg. 2000 mm. the programm calculates on the basis, deviding this lenght by the parts, eg. 490 which is brilliant. but now i need a loop so that the result gets verified: eg.

1) 2000 mm - 2x175 mm = 1650 mm as my test lenght.
2) INT(1650/490) = 3
3) INT((1650-3*490)/310) = 0
4) INT((1650-3*490-0*310)/190) ) = 0
5) INT((1650-3*490-0*310-0*190)/155) = 1
6) INT((1650-3*490-0*310-0*190-1*155)/65) = 0

Output:
7) 1650-3*490-0*310-0*190-1*155-0*65 = 25

Gap:
8) 25/(1+ 3+0+0+1+0) = 5

And here i need the loop because 5 is below 10. And the result in the gap needs to be above 10.

I hope that you can help me!

[Beamernsw]

If you did what I suggested in my last post, I think you'd find that it does work.
Here's you're last sheet with the changes I suggested along with the length changes you added.
If you wish to see row 3, then select rows 2 to 4, right click, select unhide.

[lotofspam]

Hello, unfortunately the calculation is wrong in your table.

if i eg input 1969, i get 2x175,3x490 and 1x65. space 34 and gap 16,8. BUT. 1969-2x175 is 1619. 1619-3x490-1x65 is 84, not 34, so space is wrong. and gap is 16,8.

[Beamernsw]

Sorry, you are absolutely right. I forgot to add in the 10mm gaps that I allowed for each piece. Modified formulas in H and I should fix it I hope