15,114,643 members
1.00/5 (1 vote)
See more:
Hi I am wondering how to handle #ERROR, NAN and INFINITY in a single expression of SSRS reprot when my formula is (A/B)-1). I have used the following expression to handle NAN and INFINITY which worked perferctly fine.

=iif ( ( ((A/B)=0) OR B=0 ), 0, (A/B)-1) )

( Says that if A/B=0 or B=0 then display 0 else display (A/B)-1=0 )

With this code I am getting #ERROR in my report when it divides 0/0 as the value of a and b is zero. My problem is to handle # ERROR also in this expression. Please help.
Posted
Updated 10-Aug-21 13:05pm
v3

## Solution 1

Simply try:
VB
`=iif(B=0, 0, (A/B)-1))`

You need to make sure it's a valid number.
Dhivya.J 26-Feb-13 7:47am

Thank you for answering. This only handles NAN, not infinity and #Error. I need to handle all these three(NAN,Infinity,#ERROR) in this expressions.
Sandeep Mewara 26-Feb-13 8:02am

Why so? For infinity, denominator has to be non-zero which is checked.
For #Error, it should be a valid expression, which it is.

To me all is handled, not sure what other thing you are looking for or facing.
Dhivya.J 27-Feb-13 0:52am

My report contains float values. For me the below expression worked.

=IIF( (B=0 OR A=0),0, ( ( A/IIF(B=0,1,B) )-1 ) ).

## Solution 2

(A/IIF(B<=0,1,B))-1

The above solution will handle all issue, because 0/anything is zero, so it will be automatically handled. We have to handle divide by zero error only
Dhivya.J 29-Mar-13 0:58am

Thank you:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 245 phil.o 90 Rick York 85 Richard Deeming 75 Chris Copeland 65
 OriginalGriff 5,451 Richard MacCutchan 2,428 Richard Deeming 1,530 CHill60 1,128 Rick York 875

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900