Results 1 to 10 of 10

Formula for decreasing value.

Threaded View

  1. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Formula for decreasing value.

    Okay, Try this UDF solution, will work for both scenarios
    Function CutBoard(StrtLeng, FrstCutLeng, CutIncr, Optional NumCutsPer, Optional IncldCurf, Optional CurfSz, Optional ReturnOpt)
        Dim LengthRemain, LastCut, NewCutLen
        Dim i, TotalCuts As Integer
        Dim ErrMess As String
        Dim EXIT_FLAG As Boolean
        If IsMissing(NumCutsPer) Then NumCutsPer = 1
        If IsMissing(IncldCurf) Then IncldCurf = 0
        If IsMissing(CurfSz) Then CurfSz = 1 / 8
        If IsMissing(ReturnOpt) Then ReturnOpt = 0
        ErrMess = ""
        If StrtLeng <= 0 Then
            ErrMess = "Invalid Starting Length"
        ElseIf FrstCutLeng <= 0 Or FrstCutLen > StrtLeng Then
            ErrMess = "Invalid First Cut Length"
        ElseIf CutIncr <= 0 Or CutIncr > FrstCutLeng Then
            ErrMess = "Invalid Cut Increment"
        ElseIf NumCutsPer < 1 Then
            ErrMess = "Cuts per cut length MUST be at Least 1"
        ElseIf IncldCurf <> 1 And IncldCurf <> 0 Then
            ErrMess = "Include Curf is not 1 or 0"
        ElseIf ReturnOpt < 0 Or ReturnOpt > 3 Then
            ErrMess = "Return Option MUST be 0,1,2 or 3"
        End If
        If ErrMess <> "" Then
            CutBoard = ErrMess
            Exit Function
        End If
        EXIT_FLAG = False
        TotalCuts = 0
        LengthRemain = StrtLeng
        NewCutLen = FrstCutLeng
        While LengthRemain > 0 And NewCutLen > 0 And EXIT_FLAG = False
        
            LastCut = NewCutLen
            If LengthRemain - (NewCutLen * NumCutsPer) > 0 Then
                LengthRemain = LengthRemain - (NewCutLen * NumCutsPer) - (IncldCurf * CurfSz * NumCutsPer)
                NewCutLen = NewCutLen - CutIncr
                TotalCuts = TotalCuts + NumCutsPer
            Else
                EXIT_FLAG = True
                For i = 1 To NumCutsPer
                    If LengthRemain - NewCutLen > 0 Then
                        LengthRemain = LengthRemain - NewCutLen - (IncldCurf * CurfSz)
                        TotalCuts = TotalCuts + 1
                    End If
                Next i
            End If
        Wend
        Select Case ReturnOpt
            Case 0
                CutBoard = TotalCuts
            Case 1
                CutBoard = LengthRemain
            Case 2
                CutBoard = LastCut
            Case 3
                CutBoard = "Total cuts : " & TotalCuts & " Last Cut Was : " & LastCut & " Length Remaining : " & LengthRemain
        End Select
    End Function
    to use, allow Macros,open VBA editor (Alt-F11), Insert Module, Copy & paste above into the module,save close editor

    Parameters are
    StrtLeng, FrstCutLeng, CutIncr, Optional NumCutsPer, Optional IncldCurf, Optional CurfSz, Optional ReturnOpt
    StrtLeng - Starting length of your board, in decimal inches (ie 100 1/2 inches = 100.5)
    FrstCutLeng - Length of your first cut(s) in decimal inches
    CutIncr - length to decrease cut length by in decimal inches
    NumCutsPer (Optional) - how many cuts to make at each Length (defaults to 1)
    IncldCurf (Optional) - 1 = include saw curf,0 = don't include saw curf (defaults to 0)
    CurfSz (Optional) - size of the saw blade curf in decimal inches (defaults to 0.125 (1/8"))
    ReturnOptions (optional) 0- Total number of cuts made, 1- length of board left (if any) after as many cuts can be made using first 4 parameters, 2- the length of the last cut, in decimal inches, 3- a brief text summary of options 1,2&3 (defaults to 0)

    See attachment for examples

    Hope this helps
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1