Use excel functions in vb

[andrewsmd]

I need to use the excel function normsdist in vb. I have this in my code

Imports Microsoft.Office.Interop

'this is a global variable for the excel application
Dim xl As Microsoft.Office.Interop.Excel.Application

Dim percentile as double
percentile = percentile = xl.WorksheetFunction.NormSDist(100)

output.text = percentile

However I get this error

Server Error in '/' Application.
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 46: 'percentile = (1 / (Math.Sqrt(2 * 3.1415926535897)) * (2.718281828 ^ ((zScore * -1) ^ 2 / 2)))
Line 47:
Line 48: percentile = xl.WorksheetFunction.NormSDist(100)
Line 49:
Line 50: output.Text = "L = " & l & "<br>M = " & m & "<br>S = " & s & "<br>BMI = " & bmi & _

Anyone know why? I'm open to calculating this the old fasion way, I just could not figure out what the exp in this equation meant

Z(x) = (1/(sqrt(2*pi()))*exp(-x^2/2))

My interpretation
percentile = (1 / (Math.Sqrt(2 * 3.1415926535897)) * ? ^ ((100 * -1) ^ 2 / 2))). That could be way off though. Thanks,

[marcel]

Dim xl As New Microsoft.Office.Interop.Excel.Application

should do the trick

[andrewsmd]

Yes it did. I ended up having to figure out the function manually though as the server did not have excel installed. Here is the code in case anyone ever needs it.

'this function is the same as normsdist(x) in excel
Private Function calcND(ByVal x As Double) As Double
Dim p As Double = 0.2316419
Dim b1 As Double = 0.31938153
Dim b2 As Double = -0.356563782
Dim b3 As Double = 1.781477937
Dim b4 As Double = -1.821255978
Dim b5 As Double = 1.330274429

Dim t As Double = 1 / (1 + p * x)
Dim z As Double = (1 / Math.Sqrt(2 * Math.PI)) * Math.Exp(-(x ^ 2) / 2)

Return 1 - z * (b1 * t + b2 * t ^ 2 + b3 * t ^ 3 + b4 * t ^ 4 + b5 * t ^ 5)

End Function