[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

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.  
0
kateebebe
Asked:
kateebebe
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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
 
Jeffrey CoachmanCommented:
<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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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
 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now