Help

Re: Single case where formula for single select does not work

Solved
Jump to Solution
143 0
cancel
Showing results for 
Search instead for 
Did you mean: 
KatelynLiz
4 - Data Explorer
4 - Data Explorer

I have a table with the following fields:

  • Capacity Required - a number 
  • Capacity Provided - a roll up sum of a linked record
  • Gap Remaining - the delta between Capacity Required and Capacity Provided

I have another field called Status that looks at the Gap Remaining and provides a single select option 

The following are all nested, so will be triggered in order: 

  • If the gap remaining is negative, the status is "overstaffed"
  • If the capacity required was 0 to begin with, the status is "N/A"
  • If gap remaining is 0 the status is "fully staffed"
  • if the gap remaining is less than the capacity required the status is "partially staffed"

The status field behaves as expected for 1207 records in my table. But now I have a record where when the gap remaining is 0 the status is partially staffed...if and only if one of the values being rolled up is ".10" and only for one specific record. If I change the value from .10 everything works, putting a .10 value in a different record also works fine. I'm including screen shots to show this. 

I have no idea what is happening and no idea how to fix it short of something super hacky in my nested if statement that checks for this one, very very very specific case and sets the status correctly. 

 

Field working correctly with no ".10" valuesField working correctly with no ".10" valuesNot working with a .10 includedNot working with a .10 included

Not working with ".10" included, but note record above working with a ".10" valueNot working with ".10" included, but note record above working with a ".10" value

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

I expect that this is due to a generic floating point error that applies to all computer systems, not just Airtable formulas. Your numbers have at most two digits in the decimal system. However, the computer stores numbers in binary, which may need more digits than are available in order to store the exact same value.

Try using ROUND() in your formula {Gap Remaining} formula.
ROUND( {Capacity Required} - {Capacity Provided}, 2)

See Solution in Thread

2 Replies 2
Mike_AutomaticN
10 - Mercury
10 - Mercury

Hey @KatelynLiz!

Would you mind sharing your exact formula?

Mike, Consultant @ Automatic Nation

kuovonne
18 - Pluto
18 - Pluto

I expect that this is due to a generic floating point error that applies to all computer systems, not just Airtable formulas. Your numbers have at most two digits in the decimal system. However, the computer stores numbers in binary, which may need more digits than are available in order to store the exact same value.

Try using ROUND() in your formula {Gap Remaining} formula.
ROUND( {Capacity Required} - {Capacity Provided}, 2)