i have a function in excel and i want the answer of that be max by changing variables.…
- 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
- Assistant
You can use the following VBA code to find the values of
AM3,AN3, andAO3that would maximize the associated formula (inAP3), while maintaining the constraint thatAM3+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 SubThis code will loop through all possible values of
AM3,AN3, andAO3, and set the values found to be the best for the formula inAP3. Best is the one that produces the maximum output. Note that running this code might take a while, as it tries100 * 101 / 2 = 5050combinations ofAM3,AN3, andAO3.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.