?
Solved

#Error when using iif statement with divide by zero

Posted on 2014-12-01
6
Medium Priority
?
180 Views
Last Modified: 2014-12-01
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
0
Comment
Question by:FMabey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 2000 total points
ID: 40473281
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?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473290
Nice.
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40473358
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40473367
Does it?
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40473373
no it doesn't, that will still result in Divide by zero...
0
 
LVL 3

Author Closing Comment

by:FMabey
ID: 40473886
Thank you, this works just as required.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question