By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Trying to use the NZ Function with #num! as a result

P: 5
I have a form with a calculated field (Business #1 DCR)see attachment. The field has a data Control Source of: =([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1])with a Default Value of 0. Which works fine if there are numbers in the related fields, but produces #Num! if the related fields all have the default 0. I would like result to be blank or have a 0 as a result if the related fields contain the default 0. I have tried numerous Nz calculations but have not been successful. Anyone have any ideas how to resolve this? Any help would be greatly appreciated. Thanks

Attached Images
File Type: jpg FormPic.jpg (81.3 KB, 366 views)
Jan 30 '12 #1

✓ answered by NeoPa

You describe a situation where you have Divide by Zero errors (#Num!). This is entirely unrelated to nulls in any form.

To avoid these errors you need to check for a result of zero (0) in your divisor, for such a formula is incalculable. Use IIf() to do this, as in :
Expand|Select|Wrap|Line Numbers
  1. =IIf([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]=0,0,([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]))

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,342
Nz is to be used with nulls. From the sounds of it, you don't have nulls. You're trying to divide by 0 and that's causing an error. Use the iif() function to display a different result if the denominators are 0.
Jan 30 '12 #2

NeoPa
Expert Mod 15k+
P: 31,308
You describe a situation where you have Divide by Zero errors (#Num!). This is entirely unrelated to nulls in any form.

To avoid these errors you need to check for a result of zero (0) in your divisor, for such a formula is incalculable. Use IIf() to do this, as in :
Expand|Select|Wrap|Line Numbers
  1. =IIf([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]=0,0,([N/IBus1]+[DepAmorBus1]+[IntExpBus1])/([ExistingDebtPymtsBus1]+[ProposedDebtServiceBus1]))
Jan 30 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.