# Wrong decimal formula: I can't get the right value

I use this formula field in my apex code. But I have big troubles with it, because there should be different logic for values `0` and `NULL`.

If I choose `Treat blank fields as blanks`, I'll get `NULL` even if one of the Integer filds (`First__c` or `Second__c`) is `NOT NULL`. If I choose `Treat blank fields as zeroes`, my formula field will be `0` too. And this formula is incorrect, because in both variants I get `NULL`. And I don't know why.

``````IF(First__c != NULL && Second__c != NULL, First__c + Second__c ,
IF(First__c = NULL && Second__c  != NULL, Second__c ,
IF(First__c != NULL && Second__c = NULL, First__c , NULL)))
``````

And if one of fields in `NULL` and another one is `0`, I want to get `0` in my formula field.

I don't want to change the formula type, because I have a lot of logic with it already. Can I do something with this?

• did you try to use IsBlank function? – m Peixoto Nov 26 '19 at 16:28
• The ISBLANK function is the official way to test "!= NULL" in a formula. – Phil W Nov 26 '19 at 16:31

Here is the working formula. this works in both the cases: Treat blank fields as zeroes and Treat blank fields as blanks

``````IF(
!ISBLANK(First__c) && !ISBLANK(Second__c),
First__c + Second__c,
IF(
!ISBLANK(First__c), First__c,
IF(!ISBLANK(Second__c), Second__c, 0)
)
)
``````

If any of the values is null formula returns the nonnull value.

If both of the values are null your formula will result in 0. If you want it to be null in this case replace `0` in formula with `null`

• Thank you for your help – Apelsinova Nov 26 '19 at 19:43

I did some testing in my org with fields called Number1__c and Number2__c.

Set the field to `Treat blank fields as blank fields`

use the formula:

``````IF(AND(NOT(ISBLANK(Number1__c)), NOT(ISBLANK(gnsf__Number2__c))), gnsf__Number1__c + Number2__c,
IF(AND(ISBLANK(Number1__c), NOT(ISBLANK(Number2__c))), Number2__c,
IF(AND(NOT(ISBLANK(Number1__c)), ISBLANK(Number2__c)), Number2__c, NULL)))
``````

I tried `IF(Number2__c = null, 0, Number2__c)` just to see what would happen. Even if the value was null, the second parameter was always returned (0 was never displayed unless Number2__c was set to 0).
Then I tried `IF(ISNULL(Number2__c), 0, Number2__c)` this worked. This means that SFDC knows the value is null, but doesn't support `number = null`.
`IsBlank` is more powerful than `IsNull` as it doesn't just look for a null value. It also supports strings which `IsNull` does not. Because of this it is best practice to use `IsBlank`
I've gotten into the habit of using the built in functions in formulas as opposed to the programmer syntax. For instance use `AND(BoolExpr1, Boolexpr2)` instead of `&&` and `NOT(BoolExpr)` instead of `!` as well as the above `ISBLANK()` instead of `= null` as these seem to be much more reliable.