#num! when divide by 0 like to display 0

[tareyj8569]

Hey all,

I wrote the below formula which works like a charm unless its 0 divide by 0 which happens.

=Sum(IIf([Pass or Fail]="Pass" And [Pay Period]="02" And [Location]="Purple",1,0))/Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0))

I have been researching and tried a couple of different things IIF(ISerror ....IIF(NZ.. few other things but I keep getting all different type of errors.

Been struggling last couple of hours so now seeking help.



As always thanks for your time and help.

[ssanfu]

Maybe try:

=Sum(IIf([Pass or Fail]="Pass" And [Pay Period]="02" And [Location]="Purple",1,0))/IIf(Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)) = 0, 1, Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)))

[tareyj8569]

! it works, I understand the first 2 sums, what is that 3rd one doing that is making it = 0? after the dividing recheck it?...just trying to understand the workings.

[ssanfu]

The Numerator is OK, it can be zero.
But the Denominator cannot be zero. So add an IIf() function; if the Denominator is zero, change it to 1.

The IIF() function syntax is

IIf(expr, truepart, falsepart) 

Breaking the statement apart, the Denominator is
IIf(Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)) = 0, 1, Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)))

IIf(
expr : Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0)) = 0
,
truepart : 1
,
falsepart : Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0))
)

Reading this, it says :
If the expression is equal to zero, return a 1 (the truepart) ELSE return the result of the calculation "Sum(IIf([Pay Period]="02" And [Location]="Purple",1,0))" (the falsepart)

The Denominator will be the result of the calculation OR will be 1 (if the calculation is zero).

[tareyj8569]

Thank you for taking the time to break it down for me.

[ssanfu]

No problem..happy to help