Jump to content
  • 0

Avoid division by zero


Gunnar360c
 Share

Question

Can't get this formula to work such that the denominator = 1 in case the field in the denominator is zero:

[@field:tbl20_Cases_PREVpcav]/ISNULL([@field:tbl10_Corp_PREV_pcav],1). The ISNULL([@field_zzzzzz],'1') does not deliver a '1' when field =0.

Does anyone know what is wrong with it? The involved fields are number fields represented as currency.

Appreciate any input!

G

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Hello! 

May I ask if by 0 you mean 0 as itself or null? I think ISNULL will return 1 if the value for your field is blank or NULL but if it is equal to zero, that means that the value is not NULL. 

If you mean 0 as a value, may I suggest this formula:

[@field:tbl20_Cases_PREVpcav]/(CASE WHEN [@field:tbl10_Corp_PREV_pcav]= 0 THEN 1
ELSE [@field:tbl10_Corp_PREV_pcav] END)

That will return 1 if the value is 0. You may add the ISNULL function should you want to return 0 if you have a blank value. 

Link to comment
Share on other sites

  • 0
1 hour ago, Flowers4Algernon said:

Hello! 

May I ask if by 0 you mean 0 as itself or null? I think ISNULL will return 1 if the value for your field is blank or NULL but if it is equal to zero, that means that the value is not NULL. 

If you mean 0 as a value, may I suggest this formula:

[@field:tbl20_Cases_PREVpcav]/(CASE WHEN [@field:tbl10_Corp_PREV_pcav]= 0 THEN 1
ELSE [@field:tbl10_Corp_PREV_pcav] END)

That will return 1 if the value is 0. You may add the ISNULL function should you want to return 0 if you have a blank value. 

He/She might be right. Should the denominator be 1 if the value is 0 or if the value is NULL? Those two are quite different. 

Try this other CASE statement if you are not sure:

CASE WHEN [@field:tbl10_Corp_PREV_pcav] = 0 
THEN

[@field:tbl20_Cases_PREVpcav]/1
ELSE
[@field:tbl20_Cases_PREVpcav]/ISNULL([@field:tbl10_Corp_PREV_pcav],1)
END

This should handle both scenarios if I am not mistaken.

 

Link to comment
Share on other sites

  • 0

Thank you  @Flowers4Algernon, @Tubby, @MayMusic, @Glitchthese solutions and the ones references for addressing denominator = zero (or null) works fine.

I learned something new again :) – A case statement can be used within a formula (did not know that), and to be particular about zero vs. null. In this case I meant zero, but it could also be null in another case. That's why my formula above did not work (the value was =0 but the formula only handled NULL). The CASE that address both is very elegant!

Since I posted the question, however, I am considering to not allow the value to be zero as the division would not deliver a meaningful percentage value, a value that should never exceed 100%, but these formulas will be valuable in several use cases.

This Forum in invaluable!

G

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...