Division by Zero

I'm getting a Division by zero error with the following expression:
Attach Pct: Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100))


I have tried the IIF but can't seem to get it to work.  
kateebebeAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
you have to place the "avg" outside of the iif


Avg(IIf([LAYER IRO PERCENT]=0,0,[Layer Attachment]/[LAYER IRO PERCENT])/[TIV]*100)
0
 
Rey Obrero (Capricorn1)Commented:

use the iif this way

iif([field1]=0,0,[Field2]/[Field1])
0
 
kateebebeAuthor Commented:
iif([LAYER IRO PERCENT]=0,0,Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))

Its not working for me.  
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Jeffrey CoachmanMIS LiasonCommented:
<No points wanted>

Capricorns post basically says that you should do the "=0" for any/all fields that may be zero
...so you have to adjust your syntax accordingly.
0
 
mbizupCommented:
Depends on what you want to substitute for sero, but try this:

iif([Layer Attachment] = 0 OR [Layer IRO Percent] = 0, 0, Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))
0
 
mbizupCommented:
Got the fieldnames wrong in my earlier post above, but as pointed out by the others, you need to handle fields that can result in zeros in the denominator(s) :

iif([TIV] = 0 OR [Layer IRO Percent] = 0, 0, Avg(((([Layer Attachment]/[Layer IRO Percent])/[TIV])*100)))
0
 
kateebebeAuthor Commented:
I get the error you tried to execute a query that does not include the specified expression as part of an aggregate function.
0
 
mbizupCommented:
>> you have to place the "avg" outside of the iif

Good point!  :)
0
 
Rey Obrero (Capricorn1)Commented:
kateebebe,

did you try the post at http:#a36917561 ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.