iSixSigma

NORMSINV function not supported in Access (?)

Six Sigma – iSixSigma Forums Old Forums General NORMSINV function not supported in Access (?)

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #28996

    George Kellogg
    Participant

    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 ‘Add-In’ 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,George

    0
    #73134

    DrSeuss
    Participant

    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.
     

    0
    #73185

    George Kellogg
    Participant

    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.George

    0
    #73196

    TF
    Member

    Re: 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 Fernandes

    0
    #73208

    George Kellogg
    Participant

    Tara,
    Thank you very much!
    Have a great day
    George

    0
    #74351

    DrSeuss
    Participant

    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
Viewing 6 posts - 1 through 6 (of 6 total)

The forum ‘General’ is closed to new topics and replies.