We help IT Professionals succeed at work.

# #Error when using iif statement with divide by zero

on
300 Views
Hi all,

I have an expression on a report as follows:

=iif(sum(Fields!EstHours.Value) = 0,"N/A",sum(Fields!ACT_COMP_HRS.Value)/sum(Fields!EstHours.Value))

From time to time, the value of EstHours is zero. When this occurs, my expression returns a #Error as opposed to the N/A I require. I understand this is because the iif statement needs to evaluate the whole expression which includes the divide by zero. My question is, how do I remedy this?

Comment
Watch Question

## View Solution Only

BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Nice.
Problem resolver

Commented:
Use
=iif(sum(Fields!EstHours.Value) = 0,1,sum(Fields!ACT_COMP_HRS.Value)/sum(Fields!EstHours.Value))

to avoid the error.
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Does it?
BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
no it doesn't, that will still result in Divide by zero...

Commented:
Thank you, this works just as required.
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.