Skip to content
TheChatArchiveThe Chat Archive
gpt-4-0314· OpenAIAi2 WildChat-4.8M (AI2)

i have a function in excel and i want the answer of that be max by changing variables.…

  1. User

    i have a function in excel and i want the answer of that be max by changing variables. the variables are in am3 to ao3.and target cell is in ap3.dont use solver. and i have a constraint that am3+an3+ao33=100.write a vba to find the value of am3 an3 and ao3

  2. Assistant

    You can use the following VBA code to find the values of AM3, AN3, and AO3 that would maximize the associated formula (in AP3), while maintaining the constraint that AM3 + AN3 + AO3 = 100:

    Sub MaxFunction()
    
        Dim am3 As Double, an3 As Double, ao3 As Double
        Dim maxValue As Double, tmpValue As Double
        
        Application.ScreenUpdating = False
        
        maxValue = 0
        
        For am3 = 0 To 100
            For an3 = 0 To (100 - am3)
                ao3 = 100 - am3 - an3
                
                ' Put calculated values in cells AM3, AN3, and AO3
                Range("AM3") = am3
                Range("AN3") = an3
                Range("AO3") = ao3
                
                ' Calculate the value of AP3 with these values
                tmpValue = Application.WorksheetFunction.Calculate
                
                ' Check if the value is currently the highest
                If tmpValue > maxValue Then
                    maxValue = tmpValue
                    best_am3 = am3
                    best_an3 = an3
                    best_ao3 = ao3
                End If
            Next an3
        Next am3
        
        ' Set the cell values to the highest value combination
        Range("AM3") = best_am3
        Range("AN3") = best_an3
        Range("AO3") = best_ao3
    
        Application.ScreenUpdating = True
    
    End Sub
    

    This code will loop through all possible values of AM3, AN3, and AO3, and set the values found to be the best for the formula in AP3. Best is the one that produces the maximum output. Note that running this code might take a while, as it tries 100 * 101 / 2 = 5050 combinations of AM3, AN3, and AO3.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.