Created function return value 0 only

[love_sick7]

Hi all,

I had created a function based on the formula as attached but my function return value of 0 only. Please advise where I had gone wrong? Thanks.

MultimodalFnc.jpg

Function MM(ByVal number As Double)

x1 = 1
For i = 1 To 6
x1 = x1 * Sin(number * Pi * 0.05)
Next i

x2 = Power((number - 10) / 80, 2)
x3 = Power(2, 2 * x2)

MM = -2 * (x1 / x3)

End Function

[Norie]

Can you give some examples of the numbers you are passing to the function?

[ragulduy]

Your function will take Pi to be a variable, equal to 0 as it is not defined. Therefore number*pi*0.05 will be equal to 0, sin(0) = 0, therefore x1 = x1*0 = 0 and MM = -2*0/x3 = 0. Try

Function MM(ByVal number As Double)

x1 = 1
For i = 1 To 6
x1 = x1 * Sin(number * worksheetfunction.Pi() * 0.05)
Next i

x2 = Power((number - 10) / 80, 2)
x3 = Power(2, 2 * x2)

MM = -2 * (x1 / x3)

End Function

Note, the power function doesn't work for me but that could be a version thing, I would use

x2 = ((number-10)/80)^2
x3 = 2^(2*x2)

instead

[OllieB]

The function Power and the constant Pi do not exist in Excel

try

Function MM(ByVal number As Double)

Const PI = 3.14159265358979

x1 = 1
For i = 1 To 6
x1 = x1 * Sin(number * PI * 0.05)
Next i


x2 = ((number - 10) / 80) ^ 2
x3 = 2 ^ (2 * x2)

MM = -2 * (x1 / x3)

End Function

[love_sick7]

Thanks...I need to define the Pi value. I had defined function of power.

Public Function Power(ByVal number As Double, ByVal exponent As Double) As Double
 

' number is the base value
 

' exponent is the power to the base number
 

Power = number ^ exponent
 

End Function