Formula or Macro

[Bedford]

An amazing formula was provided to me by a super member on this site, it functions very well for my needs until a recent discovery, the formula; =LET(s,SEQUENCE(3),IF(J$7="Yes",IF(D18>4500,"over",XLOOKUP(D18,1730*(s-1),s,,-1)),0)). I’m looking to add further functionality to this formula by adding yet another constant.

This formula results in the number 3 when D18 is set to 3660. The constant in the formula 1730 has a dependent cell, F42 with the formula; =IFERROR(($D$18-4)/H42,0) resulting in the number 1219. The result in F42 in this case has 3 parts being reduced to 1219 from 1730, each having a waste factor of 511, at 3 parts a total of 1533 wasted material. I work with one more constant that I’d like to add to the formula to reduce the large amount of waste, 2500 is the new constant. In the case that D18 is 3660, if the formula could consider 1-2500/2=1250-1219=31 of waste x 2, and 1-1730-1219=511 of waste to total 573 of waste it would be a more efficient waste factor. If D18 were set at 4450 it would consider 2-2500 parts that would result in less waste than if it selected 3-1730 parts. I can’t understand if a formula can effectively choose from the two constants to minimize 1) waste per part and 2) the total number of parts needed without the need of a complicated algorithm and macro.

Anyone’s input would be appreciated.

Thank you.

 

[aaewalsh]

try this

Sub MinimizeWaste()
    ' Declare variables
    Dim waste1730 As Double
    Dim waste2500 As Double
    Dim totalWaste As Double
    Dim target As Double
    Dim parts1730 As Integer
    Dim parts2500 As Integer
    Dim totalParts As Integer
    Dim wastePerPart1730 As Double
    Dim wastePerPart2500 As Double
    Dim wastePerPart As Double

    ' Set the target value
    target = Range("D18").Value

    ' Calculate waste and parts for 1730 constant
    parts1730 = (target - 4) / Range("H42").Value
    waste1730 = parts1730 * 511
    wastePerPart1730 = waste1730 / parts1730

    ' Calculate waste and parts for 2500 constant
    parts2500 = (target - 4) / 2500
    waste2500 = parts2500 * 31
    wastePerPart2500 = waste2500 / parts2500

    ' Compare waste per part and select the constant with the least waste
    If wastePerPart1730 < wastePerPart2500 Then
        totalWaste = waste1730
        totalParts = parts1730
        wastePerPart = wastePerPart1730
    Else
        totalWaste = waste2500
        totalParts = parts2500
        wastePerPart = wastePerPart2500
    End If

    ' Output results
    Range("F42").Value = totalWaste
    Range("F43").Value = totalParts
    Range("F44").Value = wastePerPart
End Sub

 

[Bedford]

try this

Sub MinimizeWaste()
    ' Declare variables
    Dim waste1730 As Double
    Dim waste2500 As Double
    Dim totalWaste As Double
    Dim target As Double
    Dim parts1730 As Integer
    Dim parts2500 As Integer
    Dim totalParts As Integer
    Dim wastePerPart1730 As Double
    Dim wastePerPart2500 As Double
    Dim wastePerPart As Double

    ' Set the target value
    target = Range("D18").Value

    ' Calculate waste and parts for 1730 constant
    parts1730 = (target - 4) / Range("H42").Value
    waste1730 = parts1730 * 511
    wastePerPart1730 = waste1730 / parts1730

    ' Calculate waste and parts for 2500 constant
    parts2500 = (target - 4) / 2500
    waste2500 = parts2500 * 31
    wastePerPart2500 = waste2500 / parts2500

    ' Compare waste per part and select the constant with the least waste
    If wastePerPart1730 < wastePerPart2500 Then
        totalWaste = waste1730
        totalParts = parts1730
        wastePerPart = wastePerPart1730
    Else
        totalWaste = waste2500
        totalParts = parts2500
        wastePerPart = wastePerPart2500
    End If

    ' Output results
    Range("F42").Value = totalWaste
    Range("F43").Value = totalParts
    Range("F44").Value = wastePerPart
End Sub

Hi Aaewalsh, thank you for responding. I have to apologize my weakness is with the macro/vba. I appreciate your hard work, but I'm not sure how to put this into action, I have never experimented or used a macro in my years of working with excel. I'll research how to implement this, and let you know my findings. I was moreover hoping for a formula solution, sorry if I've wasted your time.
Thank you,
Doug.