Help with #Num! error on a report

When I use a calculated field on a report and it is dividing a number by zero, it produces an error #Num! in the field on the report.
Is there a way to have it produce the answer whenever it is legit, but replace the #Num! with 0 or anything I decide.
I was thinking maybe IIF ?

Thanks

Bill
bmeehanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaurthsConnect With a Mentor Commented:
=IIf([SumOfmay amt]=0," ",[SumOfmay margin]/[SumOfmay amt])
0
 
PaurthsCommented:
here is an example of that:

field3: IIf([field2]=0;"0";[field1]/[field2])

cheers
Ricky
0
 
dovholukCommented:
paurths is on the money (not sure about those semi-colons though?)

i would change it around a bit to accept NULL values as well. such as:

CalculatedField : IIF(Nz(AFieldThatCouldBeZero,0) = 0, 'INF', SomeOtherField / AFieldThatCouldBeZero)

just adding to paurths comment...

dovholuk
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
bmeehanAuthor Commented:
I tried this:
=IIf([SumOfmay margin]/[SumOfmay amt]=0," ",[SumOfmay margin]/[SumOfmay amt])

and the answer is either the correct amt or I get a #Num!

I notice it is when both margin and amt = 0
Could it have something like division by 0 is not allowed?

Bill
0
 
PaurthsCommented:
u checked the division in the iif statement to a 0 value.
if sumofmay amt is yet zero then u have #num result, so it is not 0 and the true part of the iif statement is not shown. Only the false.
0
 
PaurthsCommented:
btw, exactly, division by zero can not be processed.
0
 
PaurthsCommented:
or checking for null values also (dovholuk)
=IIf(Nz([sumOfMay amt];0)=0;" ";[SumOfMay margin]/[SumOfMay amt])
0
 
bmeehanAuthor Commented:
That's it!

Thanks for the help.

Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.