Formula or Macro

Bedford

Active Member
Joined
Feb 3, 2015
Messages
333
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
try this
VBA Code:
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
 
Upvote 0
try this
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,826
Messages
6,181,192
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top