Division by Zero error

Hi,
I have the formula below used in one of my queries but I am getting this error “Division By Zero” when one of the numbers are zero.  Is there a way to fix this issue?  Thanks for your help

Annulized_Data: Sum(((([SumOfSE_TTL]/([AvgOfCASE_MIX_IDX_TTL]*([SumOfACUTE_DISCH_TTL])/([SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL]))))))

Open in new window

karinos57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
TRY

Annulized_Data:
Sum(iff([AvgOfCASE_MIX_IDX_TTL]=0,0
iif([SumOfACUTE_DISCH_TTL]=0,0
IIF([SumOfGROSS_IP_RVN_TTL]=0,0
IIF([SumOfGROSS_PAT_RVN_TTL]=0,0
,[SumOfSE_TTL]/
[AvgOfCASE_MIX_IDX_TTL]*
[SumOfACUTE_DISCH_TTL]/
[SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL])
))))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Basically, you test for values that could make a denominator zero:



Annulized_Data: IIf([AvgOfCASE_MIX_IDX_TTL]*[SumOfACUTE_DISCH_TTL]*[SumOfGROSS_IP_RVN_TTL]*[SumOfGROSS_PAT_RVN_TTL]  = 0 , 0, Sum(((([SumOfSE_TTL]/([AvgOfCASE_MIX_IDX_TTL]*([SumOfACUTE_DISCH_TTL])/([SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL])))))))



That forces the value of the expression to zero.
0
Patrick MatthewsCommented:
Lowfatspread's basic approach is probably correct; looks like I have my expression inside-out :)

Note to Lowfatspread: you can avoid the nested IIf by testing the product of [AvgOfCASE_MIX_IDX_TTL]*[SumOfACUTE_DISCH_TTL]*[SumOfGROSS_IP_RVN_TTL]*[SumOfGROSS_PAT_RVN_TTL], as if any one of them is zero, the product will be zero :)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Gustav BrockCIOCommented:
Try this.

/gustav
Annulized_Data: Sum(IIf([AvgOfCASE_MIX_IDX_TTL]*[SumOfGROSS_IP_RVN_TTL]*[SumOfGROSS_PAT_RVN_TTL]=0,0,[SumOfSE_TTL]/[AvgOfCASE_MIX_IDX_TTL]*[SumOfACUTE_DISCH_TTL]/[SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL]))

Open in new window

0
Patrick MatthewsCommented:
gustav,

I think we need to test the value of [SumOfACUTE_DISCH_TTL] as well...

Patrick
0
felipe_schauenburgCommented:
Hello karinos57,

generally if you get a division by zero it is a methematical error and no program can handle it. What you could possibly do is verify, prior to the division, if any of the values are 0 and handle it according to the business rule you are apllying.
Check the folloeing link for an exemple:

http://office.microsoft.com/en-us/access/HA010550731033.aspx

Hope it helps you.

Regards.
0
karinos57Author Commented:
Thanks everyone who tried to help me but i am still getting the error.
Lowfatspread, i can't even save the formula in the query when i use your formula
cactus_data, for somereason, when i use your formula, it is messing up some other queries
matthewspatrick, i did not try yours since you said your formula is not right.
here is the problem: The table that the query is using is sometimes BLANK OR it has the value ZERO OR i have some values.  I appreciate all your help.

0
Gustav BrockCIOCommented:
> cactus_data, for somereason, when i use your formula,
> it is messing up some other queries

That is because it returns zero for those values where you would have a divide by zero error.

For blank (Null value) fields, try this.

/gustav
Annulized_Data: Sum(IIf(Nz([AvgOfCASE_MIX_IDX_TTL]*[SumOfGROSS_IP_RVN_TTL]*[SumOfGROSS_PAT_RVN_TTL],0)=0,0,[SumOfSE_TTL]/[AvgOfCASE_MIX_IDX_TTL]*[SumOfACUTE_DISCH_TTL]/[SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL])) 

Open in new window

0
LowfatspreadCommented:
mathewpatrick   DOH! Thanks , I couldn't think how to do AND or OR in the IIF

KARINOS97

OK  so write a query to return zero for thecolumns in the table if the have blanks and use that instead of the table in this query..

e.g.  

iif([SumOfACUTE_DISCH_TTL]='',0,isnull[SumOfACUTE_DISCH_TTL,0]) as [SumOfACUTE_DISCH_TTL]

for each of the columns ....

then use
Annulized_Data: Sum(IIf([SumOfSE_TTL]*[SumOfACUTE_DISCH_TTL]*[AvgOfCASE_MIX_IDX_TTL]*[SumOfGROSS_IP_RVN_TTL]*[SumOfGROSS_PAT_RVN_TTL]=0,0,[SumOfSE_TTL]/[AvgOfCASE_MIX_IDX_TTL]*[SumOfACUTE_DISCH_TTL]/[SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL]))
0
karinos57Author Commented:
I think i know what fixes the problem.  Here is what i need to do:
if AvgOfCASE_MIX_IDX_TTL = 0 then change into Null.  Because i checked the table and found 0 values in the AvgOfCASE_MIX_IDX_TTL field and all what i did was removed the zero's and the query worked fine.
0
Gustav BrockCIOCommented:
OK, then this should do.

/gustav
Annulized_Data: Sum(IIf([AvgOfCASE_MIX_IDX_TTL]*[SumOfGROSS_IP_RVN_TTL]*[SumOfGROSS_PAT_RVN_TTL]=0,Null,[SumOfSE_TTL]/[AvgOfCASE_MIX_IDX_TTL]*[SumOfACUTE_DISCH_TTL]/[SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL])) 

Open in new window

0
Steve_Ready_Set_GoCommented:
Could you describe what it is you are trying to acheive? Perhaps you're trying to do too much in a single query.

I just tried this and it worked (even with nulls and zeros in some test data).

I first made a test table named "Table1" with the following fields and entered numeric data:

SE_TTL
CASE_MIX_IDX_TTL
ACUTE_DISCH_TTL
GROSS_IP_RVN_TTL
GROSS_PAT_RVN_TTL

I then created this query. . To try it yourself, open a new query, display the SQL view and paste this in:

SELECT Sum([Table1].SE_TTL) AS SumOfSE_TTL, Avg([Table1].CASE_MIX_IDX_TTL) AS AvgOfCASE_MIX_IDX_TTL, Sum([Table1].ACUTE_DISCH_TTL) AS SumOfACUTE_DISCH_TTL, Sum([Table1].GROSS_IP_RVN_TTL) AS SumOfGROSS_IP_RVN_TTL, Sum([Table1].GROSS_PAT_RVN_TTL) AS SumOfGROSS_PAT_RVN_TTL
FROM Table1;


Note that the fields were created in a table named "Table1". . please change the table name to match your tables name before pasting.

Save that first query as "FirstQuery"

Then open another new query, again display SQL view and paste this in: "this should look familiar"

SELECT Sum(((([SumOfSE_TTL]/([AvgOfCASE_MIX_IDX_TTL]*([SumOfACUTE_DISCH_TTL])/([SumOfGROSS_IP_RVN_TTL]/[SumOfGROSS_PAT_RVN_TTL])))))) AS Annulized_Data
FROM FirstQuery;

Save the query as "SecondQuery"
0
CCastellanosCommented:
I am not sure if in previous answers by other members thsi was explained, but I do remember someone saying this is in essence a mathematical problem. In fact the first thing you need to do is finding your "Zero-divisor offenders" or the possible quantities that will make this happen. Then as other have suggested you will need to AVOID running the division as it will be a division by zero which I believe in your case the result is non-applicable (result is defined as infinite).
Well, let's get to the core. The offending quantities are:
- AvgOfCASE_MIX_IDX_TTL
- SumOfACUTE_DISCH_TTL
- SumOfGROSS_PAT_RVN_TTL
You will need to filter these three, so if ANY of the is "0" then avoid the division. that's it. Adjust your code with IFF conditions and exit formula accordingly.
I have attached both WORD and IMAGE file of the re-aarangements I did to your formula. This gives a better visual on how to proceed.
NOW, the big question is if after untanggling your formula is the final simpler division the one you were looking for (see iamge)

Annulized-Data.JPG
Annulized-Data.doc
0
karinos57Author Commented:
tx
0
CCastellanosCommented:
karinos57,

Above everything adn foremost I am glad the community was able to provide a solution to your inquiry. Neverthless, I can't help to ask it the approach I suggested was not of relevance to the solution. If this was the case and ir order to improve how I focus my help to requesters, woudl you be so kind and share sincere pointers of why it was not to point?

Thanks, Sincerely. CCITXL
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.