Calculating Lengths

[Dan5977]

Pretty sure this one should be an easy one for you guys and I probably just need putting on the right track with it.

Can't believe I haven't solved it myself yet but here goes.

We have a product which can be supplied in any run length. This run length is divided into smaller pieces for manufacture.
Standard length (also maximum length) of a piece is 2,380mm. Minimum length of a piece is 1,200mm (unless the run length is less than 1,200mm).

Generally I need to use the standard length first and then have a make up length. For example, an 11,000mm run would be 4@2,380 + 1@1,480mm.
The complication comes when the make up length works out at less than 1,200mm. I then need 2 make up lengths.
Example - a 12,000mm run length would be 4@2,380 + 2@1,240mm.
Even then, I can handle this with some nested IF statements and that may still be the way to go but they are getting rather long and complicated when we consider the next issue.

If I get a run length of 4,770mm I can't use any standard lengths because 4,770-2,380=2,390. 2,390 being above my maximum and 2,390/2 being below my minimum. I therefore need to have 0 standard lengths and 3 make up lengths of 1,590mm instead.

This is an idea of the layout I am going for.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Qty[/TD]
[TD="align: center"]Run Length[/TD]
[TD="align: center"]Std. Lengths[/TD]
[TD="align: center"]No of Std. Lengths[/TD]
[TD="align: center"]Make Up Length[/TD]
[TD="align: center"]No of Make up Lengths[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]11000[/TD]
[TD="align: center"]2380[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1480[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]





Column 1 & 2 are inputs. Column 3 is a standard length. So I only require formulas for the last 3 columns.

There is much more going on in this spreadsheet but it's just this particular problem that I feel I am possibly going about in the wrong way as it's getting longwinded.

Any help making relatively simple formulas would be appreciated.

Thanks,

Dan.

 

[Dan5977]

Actually I think I may be overcomplicating it even more than I need to. The issue with the 4,770 length isn't a massive problem. If I reduce my minimum length down to 1,190 from 1,200 it never applies so I think I will do that and continue using my rather long nested if statements unless someone comes along with a neater solution.<d12,0,if(and(c12><d12,0,if(and(c12><d12,0,if(and(c12><d12,0,if(and(c12></d12,0,if(and(c12></d12,0,if(and(c12></d12,0,if(and(c12></d12,0,if(and(c12>

 

[tonyyy]

Dan,

If you're comfortable with a vba approach, you might consider the following...

Sub CutLengths_1024899()
Dim r As Range, rng As Range
Dim i As Integer, j As Double, k As Long
Dim minLen As Long, maxLen As Long, TotalLen As Long

On Error Resume Next
Set rng = Application.InputBox(prompt:="Please select a cell or range.", Title:="No of Std. Lengths", Type:=8)
If rng Is Nothing Then Exit Sub
If rng.Columns.Count > 1 Or rng.Column <> 4 Then
    MsgBox prompt:="Please restrict your selection to Column D."
    Exit Sub
End If
On Error GoTo errHandler
For Each r In rng
    minLen = 1200 'Adjust as needed
    maxLen = r.Offset(0, -1).Value
    TotalLen = Cells(r.Row, 1).Value * Cells(r.Row, 2).Value
    i = Int(TotalLen / maxLen) 'No. of Std. Lengths
    j = (TotalLen / maxLen - Int(TotalLen / maxLen)) * maxLen 'Make up Length
    k = 1 'No. of Make up Lengths
    
    Do
        If j < minLen Then
            i = i - 1
            k = k + 1
            j = (j + maxLen) / k
            If i * maxLen + j * k > TotalLen Or i = 0 Then
                j = TotalLen / k
                Exit Do
            End If
        Else
            Exit Do
        End If
    Loop
    
    r.Value = i
    r.Offset(0, 1).Value = j
    r.Offset(0, 2).Value = k
Next r

errHandler:
    If Err.Number = 6 Then
        MsgBox "Please ensure the Qty and Run Length cells are greater than zero (0)."
    Else
        If Err.Number <> 0 Then MsgBox "Sorry, an error occurred." & vbCrLf & vbCrLf & _
            Err.Number & ": " & Err.Description
    End If
End Sub

The code will prompt you to select a cell or range in Column D - No of Std. Lengths - then proceed to calculate the No of Std. Lengths, Make Up Length, and No of Make up Lengths.

Cheers,

tonyyy