Avoid division by zero

[KG360]

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

[Flowers4Algernon]

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. 

[Flowers4Algernon]

Oh, prolly this forum post could be helpful, too?
 

 

[Tubby]

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.
 

[KG360]

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

[cheonsa]

Hello!

Just to add, here's the link to known errors and messages and how to solve them: https://howto.caspio.com/troubleshooting/errors-and-messages/

Included here is on how to avoid dividing values with 0.

Case

When [@field:B]= '0'

Then null

Else

([@field:A]/[@field:B])

End

Hope this helps.