Solved

#Error when using iif statement with divide by zero

Posted on 2014-12-01
6
155 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
6 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 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 10

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now