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?
 
LowfatspreadConnect With a Mentor Commented:
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
 
Patrick MatthewsConnect With a Mentor Commented:
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 MatthewsConnect With a Mentor Commented:
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Gustav BrockConnect With a Mentor CIOCommented:
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_schauenburgConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.