Access Excel formulas in C#

Question

I need to use NORMSINV formula in Excel in my C# code. I dont have any contact with Excel sheet and I am not getting any values from excel. Simply need to use the formula in C# code.

How to acheive this?

To clarify, you want some C# code that performs the same function as normsinv but without actually using Excel itself? — Steve Mallam
– Steve Mallam, Commented Aug 14, 2011 at 12:10
yes exactly. Also can that formula be achieved by adding some reference of office tools to my c sharp code in header files?? — premg
– premg, Commented Aug 14, 2011 at 12:13
You would need to use the Microsoft.Office.Interop.Excel namespace - IIRC you'd need to create a spreadsheet in memory and read the answer out of the cells. You'd also need Excel installed on the PC running the code. Recommend you look at the other answer(s) identifying libraries with equivalent functionality — Steve Mallam
– Steve Mallam, Commented Aug 14, 2011 at 12:27
I am not having excel in the machine and I am not sure excel will be installed or not... — premg
– premg, Commented Aug 14, 2011 at 12:35
If you may not have Excel on the machine, you cannot use the Excel functions directly. — Steve Mallam
– Steve Mallam, Commented Aug 14, 2011 at 12:37

HaaR · Accepted Answer · 2011-08-14 13:34:22Z

2

You could use the GSL (GNU Scientific Library, written in C). The function which corresponds to the one you're after would be "gsl_cdf_ugaussian_Pinv" I believe.

You can get the GSL here:

http://ftp.heanet.ie/mirrors/gnu/gsl

Details of the function from Excel:

http://support.microsoft.com/kb/826772

And the corresponding details from the GSL:

http://www.gnu.org/s/gsl/manual/html_node/The-Gaussian-Distribution.html

(Note: The Standard Normal distribution is also known as the Unit Gaussian distribution)

edited Aug 14, 2011 at 13:34

answered Aug 14, 2011 at 12:17

HaaR

1,4532 gold badges14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

premg Over a year ago

I want to use only this function. Thanks a lot i will check and let you know

HaaR Over a year ago

I don't really know C#, but as sqrt, pow and M_PI are defined in C99 then the following should work: public double normsinv(double x) { return (1 / sqrt(M_PI)) * exp(-1 * (pow(x,2)/2)); }

HaaR Over a year ago

Apparently M_PI was dropped in C99 actually, you'll also need to define M_PI yourself: #define M_PI 3.14159265358979323846

premg Over a year ago

public double Normsinv(double x) { return (1 / Math.Sqrt(Math.PI)) * Math.Exp(-1 * (Math.Pow(x, 2) / 2)); } // This is the equavalent code but i dont know how far its correct

HaaR Over a year ago

Apologies, I gave you the PDF of a Normal Distribution, not the CDF; give me a couple of minutes and I'll write in C# the correct method for you.

|

HaaR · Accepted Answer · 2011-08-14 14:07:53Z

0

I believe this should calculate an accurate approximation of what you're after.

(Note: I don't program in C# so there might be some syntax errors)

public double pdf_norm(double x)
{
  return (1 / Math.Sqrt(Math.PI)) * Math.Exp(-1 * (Math.Pow(x, 2) / 2)); 
}

public double cdf_norm(double x)
{
  double[] bValues = [0.2316419, 0.319381530, −0.356563782, 1.781477937, −1.821255978, 1.330274429];

  double t = 1 / (1 + bValues[0]*x);

  return 1 - pdf_norm(x)*(bValues[1]*t + bValues[2]*Math.Pow(t,2) + bValues[3]*Math.Pow(t,3) + bValues[4]*Math.Pow(t,4) + bValues[5]*Math.Pow(t,5));
}

You'd then want to call the function cdf_norm to return the number you want.

Please test it works and let me know. :)

answered Aug 14, 2011 at 14:07

HaaR

1,4532 gold badges14 silver badges21 bronze badges

7 Comments

premg Over a year ago

i have passed 60 as a parameter to pdf_norm and called cdf_norm but i got 0.4963164 but it should have been 0.253

premg Over a year ago

double doubleNormsinv = cdf_norm(pdf_norm(60)); public double pdf_norm(double x) { return (1 / Math.Sqrt(Math.PI)) * Math.Exp(-1 * (Math.Pow(x/100, 2) / 2)); } //Since i am passing percentage value i am dividing it by 100 in Math.Pow

premg Over a year ago

how about this??home.online.no/~pjacklam/notes/invnorm/impl/misra/normsinv.html

HaaR Over a year ago

The input would be 0.6 for 60%, 0.7 for 70% etc, i.e. cdf_norm(0.6); also - that JavaScript function looks like it works, implementing that in C# should be easy enough for you as well.

premg Over a year ago

That javascript function works only for few numbers like till 70-80% its correctly gives the answer but after that the value is getting reduced for ex: if i give 99.99% it gives 2.4900160235815867 but it should be 3.71

|

Collectives™ on Stack Overflow

Access Excel formulas in C#

2 Answers 2

10 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related