Help

Nested IF Formulas involving Currency format field values with Decimal Points have error results

Topic Labels: Formulas
396 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Keiths_2023
4 - Data Explorer
4 - Data Explorer

Hello Everyone!

Can someone help me troubleshoot why using IF formula with arguments containing field values with decimal points return errors? All the fields are in currency format with precision of 1.00 except one with duration type field in seconds and in decimal format. 

Here's my formula and there's at least 4 different results to expect (FULLY-PAID, PARTIALLY-PAID, OVER-PAID, NO PAYMENT and N/A if all 4 logical arguments are false. Below is my formula.

 

 

IF({Total Amount Paid (ToDate)}=0,"NO PAYMENT",IF({Total Amount Paid (ToDate)}<{Total Amount Due (ToDate)},"PARTIALLY-PAID",IF({Total Amount Paid (ToDate)}={Total Amount Due (ToDate)},"FULLY PAID",IF({Total Amount Paid (ToDate)}>{Total Amount Due (ToDate)},"OVER-PAID","N/A"))))
 
 
There are only two fields used in the formula and i noticed that whenever the value of the {Total Amount Due (ToDate)} field has decimal points (ex: 875.76 , 462.41) and the value on the {Total Amount Paid (ToDate) field is the same, it results to either PARTIALLY-PAID or OVER-PAID wherein the correct result should be FULLY-PAID.
 
I got no problem whenever the value on {Total Amount Paid (ToDate) field has decimal points as long as it is NOT equal to the value of {Total Amount Due (ToDate)} field that has a value with a decimal point.
 
Maybe someone from the experts on writing formulas can help me correct the error. Please see below screenshot of my table for your reference.
 
IF conditional formula ERROR 2023-09-25 18 20 15.jpg
 
 
Thank you in advance for the help.
 
 

1 Reply 1
Steve_Haysom
8 - Airtable Astronomer
8 - Airtable Astronomer

This is probably because even though the fields are displaying to 2 decimal places, at least one of your numbers is being stored to more than 2 decimal places, so when comparing them they are not the same number. Try the round function:

...IF(ROUND({Total Amount Paid (ToDate)}, 2)=ROUND({Total Amount Due (ToDate)},2),"FULLY PAID",...