NormsInv won't work in Access

[Danny C]

Could someone Please tell me how I can get NormSInv to
work in Access. I can't use IIf( because there are 10,000
numbers and it overloads the queries. NormSInv(MyNumbers)
+1.5 is suppose to give me a Z-Score on waste percentage.
I'm no expert and was given this formula to use by the
Sixers. All I get is an error in my report and NormSInv
pops up before the report opens. I've added Excel to the
libraries at the modules, but it is not in my built in
functions. Please help! Thanks!

 

[None]

NormSInv is an MS Excel function. Norder 2get it 2 work in
Ms Access make sure u set a reference 2 the Ms excel
object. go n 2 a module and click Tools | References then
choose the Excel Object Library

 

[Guest]

I have referenced Excel 9.0. But it still won't work in
my reports. =NormSInv(Waste%)+1.5- returns an error in
the text box. Waste% = (Number)/(Number)*100--- fixed
format, 2 decimal places. I have until Tueday to figure it
out, otherwise, I'll be writing them in from Excel
worksheet. Thanks for trying.

 

[Tim Ferguson]

Could someone Please tell me how I can get NormSInv to
work in Access.

Rats! I have a VBA version of the NormSDist calculation (i.e. start with a
z-score and convert it to a percentile) but I can't find the source to see
if the Inverse function is available.

Best of luck


Tim F

 

[Douglas J. Steele]

If you have a reference set to Excel, you can use its functions as
Excel.WorkSheetFunction.FunctionName. For instrance, if you type:

?Excel.WorksheetFunction.NormSInv(0.908789)
1.33333514895639

To use an Excel function in a query, you need to make a wrapper function by
that will pass the data to the Excel function and return the results. For
example:

Function MyNormSInv(Probability As Single)
MyNormSInv = Excel.WorksheetFunction.NormSInv(Probability)
End Function

Then, call that wrapper function from queries, like:
Result: MyNormSInv([field1])