#Error when using iif statement with divide by zero

FMabey
FMabey used Ask the Experts™
on
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?

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
BI Consultant
Most Valuable Expert 2011
Commented:
Your understanding of the issue is correct.  One way to avoid the error is to write this code through the Custom Code functionality, which means you'll need a couple of lines of code in Visual Basic.

Or you can add an additional IIF following the /, like this:

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

More info: FAQ: Why does the “Attempted to divide by zero” error still happen?
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Nice.
HuaMin ChenProblem resolver

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

Open in new window


to avoid the error.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Does it?
ValentinoVBI Consultant
Most Valuable Expert 2011

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

Author

Commented:
Thank you, this works just as required.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial