NORMSINV function not supported in Access (?)
Six Sigma – iSixSigma › Forums › Old Forums › General › NORMSINV function not supported in Access (?)
 This topic has 5 replies, 3 voices, and was last updated 20 years, 1 month ago by DrSeuss.

AuthorPosts

March 12, 2002 at 7:35 pm #28996
George KelloggParticipant@GeorgeKellogg Include @GeorgeKellogg in your post and this person will
be notified via email.I am trying to place a Six Sigma rating in a MS Access 2000 Report. I understand the example for Excel using the NORMSINV function, but it would appear that it is not supported in Access, at least by default.
Are there any AddIn or References that can be brought into Access to give it this functionality?
and
Where can I get an exact explanation/example of the calculations used to determine the Inverse of the standard cumulative distribution (not an example of the NORMSINV function, but rather, the specific calculations that are performed on the number passed to it).
Thank you,George0March 12, 2002 at 7:55 pm #73134
DrSeussParticipant@DrSeuss Include @DrSeuss in your post and this person will
be notified via email.You will have to contact Microsoft to get the specifics on what you are asking for.
I know Excel supports DDE and the other dynamic data linking capability. I would assume Access has similar DDE capability. You should be able to link your field from Access to an Excel cell or formula. Check out the Excel functions & macro manual. It is the second one that most people loose because they never have an opportunity to use it. Otherwise, ask Bill Gates……….smile.
0March 13, 2002 at 7:33 pm #73185
George KelloggParticipant@GeorgeKellogg Include @GeorgeKellogg in your post and this person will
be notified via email.Thank you for the quick response! I will try the DDE suggestion and see if I can get around the problem that way.
Please let me know if you (or any one else) ever run across the calculations for determining the Inverse of the standard cumulative distribution.
Thanks again.George0March 14, 2002 at 11:20 am #73196Re: Process Sigma Calculation – Inverse normal
I’ve noticed a lot of people are looking for an algorithm to compute the inverse normal, and I’ve found a site that has the info.
Check out: http://www.math.uio.no/~jacklam/notes/invnorm/
This was a link posted in response to a similar thread a couple of months ago.
Good luck
Tara Fernandes0March 14, 2002 at 1:52 pm #73208
George KelloggParticipant@GeorgeKellogg Include @GeorgeKellogg in your post and this person will
be notified via email.Tara,
Thank you very much!
Have a great day
George0April 15, 2002 at 7:52 pm #74351
DrSeussParticipant@DrSeuss Include @DrSeuss in your post and this person will
be notified via email.Someone asked about the formulas for calculating Normsinv & Normsdist using a spreadsheet like Excel. I finally found the reference formulas:
P = [(1+C1Z+C2Z2+…+C6Z6)16]/2, note the nbrs after the Cs are subscripts and superscripts after the Zs. The 16 is an exponent for the entire polynomial. I know it appears a little awkward, you can blame my lack of HTML font writing capability.
Z and P are 1 tailed. Z is the standard normal deviate, P is the tail area probability. C1=0.0498673470, C2=0.0211410061, C3=0.0032776263, C4=0.0000380036, C5=0.0000488906, C6=0.0000053830
Z = L – (C1+C2L+C3L2)/(1+C4L+C5L2+C6L3) again the nbr after the Cs are subscripts and the nbrs after the L (lambda) are superscripts. L = sqrt[ ln (1/P2)], the 2 is a superscript. The coefficients for the second formula are C1=2.515517, C2=0.802853, C3=0.010328, C4=1.432788, C5=0.189269, C6=0.000308
Both formulas are Chebychev approx polynomials for Z and Prob in the Normal curve tail. If my memory serves me, these approximations are more accurate for Z values greater than 4.5. The formulas can take you out to Z = 9 or 10. I really don’t have a practical reason for calculating Z out to those decimal places, but the formulas should work. Note, most commercial software will stop at Z=6 or 7, anything larger really doesn’t buy anything. Good luck!0 
AuthorPosts
The forum ‘General’ is closed to new topics and replies.