Evenly split a length into the lowest number of equal lengths

[Strathe]

I have a product which comes in sections. Its total length, and the sections it gets broken into, are at issue.

In some situations we will need to take an arbitrary product length (lets say 28000mm), and break it into an arbitrary number of equal sections.

I've been banging my head against it trying to solve it using GCD, but I think I am on the wrong track and I would appreciate some fresher minds having a look at it.

The rules for breaking it up are.

1) The longest any one section can be is 2800.
2) The total length will always be a multiple of 280.


The output I am looking for is the smallest number of sections the total length can be evenly broken into. From that I can get to everything else.

It's got to the frustration point where I don't even know if what I am looking for is possible!

 

[Yongle]

Is this what you want?


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Total[/td][td]Pieces[/td][td]Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

560​

[/td][td]

1​

[/td][td]=INT(A2/2801)+1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

1120​

[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1680​

[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

2240​

[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

2800​

[/td][td]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

3360​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

3920​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

4480​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

5040​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

5600​

[/td][td]

2​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

6160​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

6720​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

7280​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

7840​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

8400​

[/td][td]

3​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

8960​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

9520​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

10080​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

10640​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

11200​

[/td][td]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

22​

[/td][td]

11760​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

23​

[/td][td]

12320​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

24​

[/td][td]

12880​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

25​

[/td][td]

13440​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

26​

[/td][td]

14000​

[/td][td]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

27​

[/td][td]

14560​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

28​

[/td][td]

15120​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

29​

[/td][td]

15680​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

30​

[/td][td]

16240​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

31​

[/td][td]

16800​

[/td][td]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

32​

[/td][td]

17360​

[/td][td]

7​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

33​

[/td][td]

17920​

[/td][td]

7​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

34​

[/td][td]

18480​

[/td][td]

7​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

35​

[/td][td]

19040​

[/td][td]

7​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

36​

[/td][td]

19600​

[/td][td]

7​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet3[/td][/tr][/table]

 

[Rick Rothstein]

Is this what you want?

[TABLE="class: head"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]

​

[/TD]
[TD]=INT(A2/2801)+1[/TD]
[/TR]
</tbody>[/TABLE]

My gut tells me using 2801 could produce a wrong value under some circumstances (no proof... just a gut feeling). I believe the following would always work correctly...

=INT(A2/2800)+(MOD(A2,2800)>0)

 

[Yongle]

@Rick Rothstein - you are correct
- which only matters in practical terms if the total length to be chopped up ever exceeds approx 1.5km

An alternative without using MOD

=INT((A2-1)/2800)+1

 

[Strathe]

Ah, small corollary. There is data validation that ensures only multiples of 280 can be fed into the formula, as issues like 2801 would break a whole load of things.

 

[Strathe]

Ah, small corollary. There is data validation that ensures only multiples of 280 can be fed into the formula, as issues like 2801 would break a whole load of things.

Ah, the real reason you used 2801 twigged, don't mind me. Max total run length is 49840, so we don't have to worry about Yongle's 1.5km break point.

Thank you for your suggestions, all three variant formulas return work out for total lengths up to 28000. Over 28000 they break the max 2800 rule, but I don't think trying to break it evenly beyond that is necessary.

Thankyou!

 

[Rick Rothstein]

Over 28000 they break the max 2800 rule

Can you give us an example of this?