excel calculations in Access

  • Thread starter Thread starter sdg8481
  • Start date Start date
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
 
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
 
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...
 
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
 
Back
Top