excel calculations in Access

S

sdg8481

Hi,

I have a complex query written in Excel which i want to replicate in Access,
however when i do it flags a error message saying undefined functions. So i'm
looking for the eqivualents in Access. The ones it can't find are:

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]<389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks
 
V

vanderghast

SQRT(x) is x ^0.5

Inverse and Chi (square) distribution and Normal (0,1) distribution ? FMS
inc. was having a statistical package few years ago (and probably still have
one),


You can Bing (or Google) and find some interesting algorithm, such as
http://home.online.no/~pjacklam/notes/invnorm/ or Wikipedia (for Inverse of
Chi square, solution based on Gamma function, which again, can be found in
many places, such as at http://www.rskey.org/gamma.htm)





Vanderghast, Access MVP
 
S

sdg8481

Hi,

Thank you for your reply, but unfortunatly i'm not sure i understand. I'm
looking to build the ChiINV and Normsinv into my access query. you mentioned
a statistical package where can i find this, and that first link appears to
be broken.

Thanks thou

vanderghast said:
SQRT(x) is x ^0.5

Inverse and Chi (square) distribution and Normal (0,1) distribution ? FMS
inc. was having a statistical package few years ago (and probably still have
one),


You can Bing (or Google) and find some interesting algorithm, such as
http://home.online.no/~pjacklam/notes/invnorm/ or Wikipedia (for Inverse of
Chi square, solution based on Gamma function, which again, can be found in
many places, such as at http://www.rskey.org/gamma.htm)





Vanderghast, Access MVP



sdg8481 said:
Hi,

I have a complex query written in Excel which i want to replicate in
Access,
however when i do it flags a error message saying undefined functions. So
i'm
looking for the eqivualents in Access. The ones it can't find are:

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with
Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]<389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with
Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks
Click to expand...
Click to expand...
 
K

KenSheridan via AccessMonster.com

If you return a reference to the Microsoft Excel object Library (Tools |
References on the VBA menu bar), this will expose the Excel worksheet
functions within the Access application. You can then write functions in
Access to call them, so the Access functions can then be called in the query.
I've tried creating the following functions, and they seem to work fine:


Public Function NORMSINV(dblProbability As Double) As Double

NORMSINV = Excel.WorksheetFunction.NORMSINV(dblProbability)

End Function


Public Function CHIINV(dblProbability, intDegrees_Freedom As Integer) As
Double

CHIINV = Excel.WorksheetFunction.CHIINV(dblProbability,
intDegrees_Freedom)

End Function


Ken Sheridan
Stafford, England
Hi,

I have a complex query written in Excel which i want to replicate in Access,
however when i do it flags a error message saying undefined functions. So i'm
looking for the eqivualents in Access. The ones it can't find are:

SQRT - Which i believe is just SQR in access
CHIINV
NORMSINV

So if anyone can help with this i'd be most grateful. If needed the full
access query as it stands at the minute is as follows;

Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings
with Calcs]![Calc2 Total]/[T03 - Workings with Calcs]![SumOfStaTOT]^2/[T03 -
Workings with Calcs]![SumOfObsTOT])*(IIf([T03 - Workings with
Calcs]![SumOfObsTOT]=0,0,IIf([T03 - Workings with
Calcs]![SumOfObsTOT]<389,CHIINV(0.5+95/200,2*[T03 - Workings with
Calcs]![SumOfObsTOT])/2,[T03 - Workings with
Calcs]![SumOfObsTOT]*(1-1/(9*[T03 - Workings with
Calcs]![SumOfObsTOT])-NORMSINV(0.5+95/200)/3/SQRT([T03 - Workings with
Calcs]![SumOfObsTOT]))^3))-[T03 - Workings with Calcs]![SumOfObsTOT])*100000

Any help would be gratefully recieved.

Many Thanks
Click to expand...
 
K

KenSheridan via AccessMonster.com

PS: the body text of each of the functions is a single line. Your
newsreader is likely to have split the last one at least over two lines.

Ken Sheridan
Stafford, England
 
S

sdg8481

Thank you Ken, this sounds like it will do the trick....However, i'm sorry
for being dumb, but how do i actually call these functions in query
expression, so that each row (set of data) returns its true value.

Thank you, and as i said sorry if this is a dumb question
 
K

KenSheridan via AccessMonster.com

Having created the reference to the Excel object library, you then paste the
two functions into any standard module in the database. Just open any
standard module, either an existing one or a new one, and paste the code from
my first reply below the two existing lines in the modules 'declarations'
area, not forgetting to remove any unwanted line breaks which your newsreader
might have inserted. They'll then be inserted as functions which will
available anywhere in the database.

In the query you call them in the same way as any built in Access function.
Your expression is pretty complex, so I'll have to assume that its correctly
computing the values of the arguments for the two functions, but as I've
called them by the same names as the Excel functions all you should need to
do with it is change the reference to the Excel SQRT function to the
equivalent Access SQR function.

Ken Sheridan
Stafford, England
 
Top