iterative function

[excelnovicehouston]

I am looking for how to set up a iterative function that returns the max value for example

function maxit(a)

For b = 10, 11, 12, 13

value&"_"&b = b* a + -b^2 +3

maxit = application.max(value_10, value_11, value_12, value_13)

end function

Many thanks for any help on the language around this
I am only looking for the discreet values 10,11,12,13 not a range in between them.

 

[VoG]

Try

Function maxit(a)
Dim b As Integer, y As Double
For b = 10 To 13
    y = b * a + -b ^ 2 + 3
    If y > maxit Then maxit = y
Next b
End Function

 

[excelnovicehouston]

how would i do if I wanted the iterations to be .5 instead of by integer?

 

[Andrew Fergus]

Hi

If you are seeking the maximum value as a result of the formula in your post, then that will be your highest input value wouldn't it? If b is constant then the highest value of a will return the highest resultant value, and vice versa. If both a na db a reinput values, then the highest resultant value would be with the highest values of a and b, given -b^2 = b^2.

Andrew

 

[VoG]

Try

Function maxit(a)
Dim b As Double, y As Double
For b = 10 To 13 Step 0.5
    y = b * a + -b ^ 2 + 3
    If y > maxit Then maxit = y
Next b
End Function

 

[excelnovicehouston]

Many thanks for the help VoG, my original post was a little off - I want it to display b, that maximizes y, not the y value.

Andrew, many thanks for your help. i am aware that in this simple quadratic the max will be 13, but it is only for illustrative purpose. i am using a much more complex equation that i didn't want to type.

 

[VoG]

Try

Function maxit(a)
Dim b As Double, y As Double, z As Double
For b = 10 To 13 Step 0.5
    y = b * a + -b ^ 2 + 3
    If y > z Then
        z = y
        maxit = b
    End If
Next b
End Function

 

[excelnovicehouston]

you're a god - VoGod