Standard Lengths from Multiple Dimensions

[RiNaZ]

Hello, I am not sure if i have a math problem or an excel problem, but i was hoping someone can help me with my question.

Let say i have a table with multiple lengths of wood planks that is needed:

20 in
40 in
80 in
100 in

I need to find out a common lengths that can be fit up together to make up the lengths provided in the table. In this example, the most logical answer in my opinion, from just looking at this, seems to be:

20 in = 20 in (1 unit)
40 in = 40 in (1 unit)
80 in = 40 in (2 unit)
100 in = 40 in (2 unit) + 20 in (1 unit)

So, in this case, i need to buy five 40" wood planks, and two unit of 20" wood planks. Or i can just buy twelve unit of 20" wood planks.

this seems easy enough, but what if you have a list of a thousand different multiple lengths and you have to find a common or standard lengths?

 

[Faseeh]

I suggest you to setup a sheet and use Solver to get the solution.

 

[Hui]

Are you sure it shouldn't be:

20 in = 20 in (1 unit)
40 in = 40 in (2 unit)
80 in = 40 in (4 unit)
100 in = 80 in (4 unit) + 20 in (1 unit)

?

 

[XLPadawan]

Hello, I am not sure if i have a math problem or an excel problem, but i was hoping someone can help me with my question.

Let say i have a table with multiple lengths of wood planks that is needed:

20 in
40 in
80 in
100 in

I need to find out a common lengths that can be fit up together to make up the lengths provided in the table. In this example, the most logical answer in my opinion, from just looking at this, seems to be:

20 in = 20 in (1 unit)
40 in = 40 in (1 unit)
80 in = 40 in (2 unit)
100 in = 40 in (2 unit) + 20 in (1 unit)

So, in this case, i need to buy five 40" wood planks, and two unit of 20" wood planks. Or i can just buy twelve unit of 20" wood planks.

this seems easy enough, but what if you have a list of a thousand different multiple lengths and you have to find a common or standard lengths?

One approach would be to use the GCD function to find the Greatest Common Divisor of a group of numbers, but that wouldn't be helpful if you have a lot of prime numbers in your list (or a really small GCD). Take a look at what I've uploaded for a better approach (sorry, it doesn't scale well but maybe it'll give someone sharper than me an alternate idea of how to approach the problem). I do think the MOD function will be needed in your solution. (MOD gives you the remainder from a division operation...e.g. 6MOD4=2. A MOD of 0 means no remainder and thus the dividend was evenly divisible by the divisor...e.g. 8MOD4=0).

 

[RiNaZ]

Are you sure it shouldn't be:

20 in = 20 in (1 unit)
40 in = 40 in (2 unit)
80 in = 40 in (4 unit)
100 in = 80 in (4 unit) + 20 in (1 unit)

?

Hui, basically, i was trying to find as little standard lengths as possible with less quantity or unit. The most common length is 20 in. So the list will look like below:

20in = 20in
40in = 20in+20in
80in = 20in+20in+20in+20in
100in = 20in+20in+20in+20in+20in

This means, i have to buy a quantity of twelve 20in wood planks.

But if i had follow my original list in my post, i only have to buy a total of 8 wood planks, eventhough it consisted of two different standard lengths of 20" and 40".

In this case, i would rather go with my original list, since 8 wood planks is easier to carry than 12 wood planks.

original list:

20in = 20in
40in = 40in
80in = 40in + 40in
100in = 40in + 40in +20in

hope that clears it up a bit.

 

[RiNaZ]

One approach would be to use the GCD function to find the Greatest Common Divisor of a group of numbers, but that wouldn't be helpful if you have a lot of prime numbers in your list (or a really small GCD). Take a look at what I've uploaded for a better approach (sorry, it doesn't scale well but maybe it'll give someone sharper than me an alternate idea of how to approach the problem). I do think the MOD function will be needed in your solution. (MOD gives you the remainder from a division operation...e.g. 6MOD4=2. A MOD of 0 means no remainder and thus the dividend was evenly divisible by the divisor...e.g. 8MOD4=0).

thanks Padawan, i'll try it out and let you how it looks.