Formulas evaluate all decimals places in other formula fields
|Knowledge Article Number||000176492|
Formulas evaluate all decimals places in other formula fields even if they are not visible on the user interface. For example, consider the following scenario:
Field_A: You have a formula field (number) that performs the following calculation:
3.14 * 7.21
You will configure the formula to show 1 decimal place.
The resulting value of this formula field will be 22.6
Field_B: Next, you have a number field in the same record, which contains the value 22.6
Formula_C: You have created a formula field (text) that evaluates whether the values on these fields are the same, and depending on the result, some text is displayed on the screen.
The formula syntax is the following:
IF( Field_A = Field_B , "Same", "Not the same")
When viewing the record detail page, this formula field will display "Not the same", even though both fields display 22.6 on the user interface.
The reason this happens is because Formula_C is evaluating Field_A up to all its smallest decimal, regardless of what the field has been configured to show to the user interface.
The calculation 3.14 * 7.21 is not 22.6, but rather something like this 22.639400000000000000.
Since 22.6 is not the same as 22.639400000000000000, the field Formula_C displays "Not the same" when evaluating whether both fields contain the same value.
The workaround here is to configure the formula that evaluates the other fields, to evaluate up to a specified number of decimal places. The formula is the following:
VALUE(LEFT(TEXT(FieldName), ((FIND(".", TEXT(FieldName), 0))+x)))
Replace "FieldName" with the field where you don't want all the decimals to be evaluated and replace "x" with the number of decimals that you want to be evaluated.
In our example, we want to evaluate only the first decimal place, so our formula should look like this:
IF( VALUE(LEFT(TEXT(Formula_A), ((FIND(".", TEXT(Formula_A), 0))+1))) = Formula_B , "Same", "Not the same")
This formula will evaluate whether both values are the same, and will only take into account the first decimal of the other formula field.
On the record detail page, this formula will display "Same"