Formula to avoid #Error

[D]

Hi:

I have this formula: Q2$0:
nz((nz([oct%$])+nz([nov%$])+nz([dec%$]))/IIf(([oct]+[nov]+[dec])=0,([oct%$]+[nov%$]+[dec%$]),([oct]+[nov]+[dec]))-1)

But I still get the #Error when both, the dividend and divisor are zero; can
you please tell me how do I get the result to be 0 in this case?

Thank you,

Dan

 

[KARL DEWEY]

Test them both as you did with the divisor.
NOTE -- You will still have problems unless you change
(([oct]+[nov]+[dec])= part to avoid nulls.

 

[D]

Hi Karl:

Thank you!

So, if the ([oct%$])+nz([nov%$])+nz([dec%$] is 0 what should I 'make' it?

Thanks again,

Dan

Test them both as you did with the divisor.
NOTE -- You will still have problems unless you change
(([oct]+[nov]+[dec])= part to avoid nulls.
--
KARL DEWEY
Build a little - Test a little

Hi:

I have this formula: Q2$0:
nz((nz([oct%$])+nz([nov%$])+nz([dec%$]))/IIf(([oct]+[nov]+[dec])=0,([oct%$]+[nov%$]+[dec%$]),([oct]+[nov]+[dec]))-1)

But I still get the #Error when both, the dividend and divisor are zero; can
you please tell me how do I get the result to be 0 in this case?

Thank you,

Dan

 

[Marshall Barton]

I have this formula: Q2$0:
nz((nz([oct%$])+nz([nov%$])+nz([dec%$]))/IIf(([oct]+[nov]+[dec])=0,
([oct%$]+[nov%$]+[dec%$]),([oct]+[nov]+[dec]))-1)

But I still get the #Error when both, the dividend and divisor are zero; can
you please tell me how do I get the result to be 0 in this case?

Try something more like:

IIf(Nz([oct]) + Nz([nov]) + Nz([dec]) = 0, 0, (Nz([oct%$]) +
Nz([nov%$]) + Nz([dec%$])) / (Nz([oct]) + Nz([nov]) +
Nz([dec])))

 

[D]

Thanks a lot Marsh!

Dan

I have this formula: Q2$0:
nz((nz([oct%$])+nz([nov%$])+nz([dec%$]))/IIf(([oct]+[nov]+[dec])=0,
([oct%$]+[nov%$]+[dec%$]),([oct]+[nov]+[dec]))-1)

But I still get the #Error when both, the dividend and divisor are zero; can
you please tell me how do I get the result to be 0 in this case?

Try something more like:

IIf(Nz([oct]) + Nz([nov]) + Nz([dec]) = 0, 0, (Nz([oct%$]) +
Nz([nov%$]) + Nz([dec%$])) / (Nz([oct]) + Nz([nov]) +
Nz([dec])))

 

[Duane Hookom]

You have way too many symbols in your column/field names :-(
If you want to protect against division by zero then use an expression like:
IIf({my non-null expression} = 0, 0, {another expression}/{my non-null
expression})

Keep in mind that if any of these fields is Null then the entire expression
is Null
([oct]+[nov]+[dec])
You can't compare a Null to 0 since Null is unknown.

I have concerns that your table structure is not normalized.

--
Duane Hookom
Microsoft Access MVP

Hi Karl:

Thank you!

So, if the ([oct%$])+nz([nov%$])+nz([dec%$] is 0 what should I 'make' it?

Thanks again,

Dan

Test them both as you did with the divisor.
NOTE -- You will still have problems unless you change
(([oct]+[nov]+[dec])= part to avoid nulls.
--
KARL DEWEY
Build a little - Test a little

Hi:

I have this formula: Q2$0:
nz((nz([oct%$])+nz([nov%$])+nz([dec%$]))/IIf(([oct]+[nov]+[dec])=0,([oct%$]+[nov%$]+[dec%$]),([oct]+[nov]+[dec]))-1)

But I still get the #Error when both, the dividend and divisor are zero; can
you please tell me how do I get the result to be 0 in this case?

Thank you,

Dan

 

[D]

Thank you Duane! Thank you everyone!

Dan

You have way too many symbols in your column/field names :-(
If you want to protect against division by zero then use an expression like:
IIf({my non-null expression} = 0, 0, {another expression}/{my non-null
expression})

Keep in mind that if any of these fields is Null then the entire expression
is Null
([oct]+[nov]+[dec])
You can't compare a Null to 0 since Null is unknown.

I have concerns that your table structure is not normalized.

--
Duane Hookom
Microsoft Access MVP

Hi Karl:

Thank you!

So, if the ([oct%$])+nz([nov%$])+nz([dec%$] is 0 what should I 'make' it?

Thanks again,

Dan

Test them both as you did with the divisor.
NOTE -- You will still have problems unless you change
(([oct]+[nov]+[dec])= part to avoid nulls.
--
KARL DEWEY
Build a little - Test a little


:

Hi:

I have this formula: Q2$0:
nz((nz([oct%$])+nz([nov%$])+nz([dec%$]))/IIf(([oct]+[nov]+[dec])=0,([oct%$]+[nov%$]+[dec%$]),([oct]+[nov]+[dec]))-1)

But I still get the #Error when both, the dividend and divisor are zero; can
you please tell me how do I get the result to be 0 in this case?

Thank you,

Dan