• 0

# Avoid division by zero

## 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

## 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.

##### Share on other sites

• 0

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

##### 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.

##### Share on other sites

• 0

Thank you  @Flowers4Algernon, @Tubby, these 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

##### Share on other sites

• 0

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.

## 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.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.