Skip to main content
Solved

How to create a 'Numbers' field WITHOUT uniform decimal precision?

  • December 22, 2019
  • 8 replies
  • 143 views
D

I need to record weight data for observations that have different levels of precision because they span a wide range and must be measured on different scales. Is there a way to allow for differing levels of decimal precision across entries in a single “Numbers” field?

For instance, to enter 4 sample measurements of: 3, 3.0, 3.02, and 3.020, Airtable makes me select a single level of decimal precision for all of the entries, so it ends up showing as something like: 3.000, 3.000, 3.020, 3.020 or 3, 3, 3, 3 or etc etc… You get the idea.
In any case, the resulting entries do not accurately reflect the actual data.

As far as I can tell, if I want to use this field for formula-based calculations, I must designate it a ‘numbers field’ and therefore must choose a single level of decimal precision for all of the entries.

Is there a way to get around this without misrepresenting my data precision and without creating separate weight fields for each level of decimal precision?

Thanks for the advice!

Best answer by Justin_Barrett

Okay, here’s the solution I came up with. Let me know how this works for your situation.

{Number} is a Single Line Text field so that I can set any precision that I want, with that precision calculated in the {Precision} field:

LEN(Number) - FIND(".", Number)

The {Double} field formula is the pretty much the same as last time, outputting a string.

(VALUE(Number) * 2) & ""

I then use that in {Double Display} to re-pad the necessary number of trailing zeroes to match the precision of the original.

IF(
    Number,
    Double &
    IF(
        NOT(FIND(".", Double)),
        "."
    ) &
    REPT(
        "0",
        Precision
        + (FIND(".", Double) = 0)
        - LEN(MID(Double, FIND(".", Double) + 1, Precision))
    )
)

When using this system yourself, you’d do something similar: have one formula that performs the actual calculation(s) you want and outputs a string, and another that reformats the result to match the original value’s precision.

8 replies

Justin_Barrett
Forum|alt.badge.img+21

There isn’t a way to format decimal numbers in Airtable with varying levels of precision, at least not while still treating them as numbers. If you convert them to a string, though, Airtable strips off any unnecessary trailing zeroes.

The formula in the {Formula} field to force the number into a string is:

Number & ""

If you need to use that number in later calculations, wrap VALUE() around the formula field reference that creates the string. Here’s one that simply doubles the value, then converts it back into a string:

(VALUE(Formula) * 2) & ""

Does this help?

D
  • Daniel_MacPhee
  • Author
  • New Participant
  • December 23, 2019

There isn’t a way to format decimal numbers in Airtable with varying levels of precision, at least not while still treating them as numbers. If you convert them to a string, though, Airtable strips off any unnecessary trailing zeroes.

The formula in the {Formula} field to force the number into a string is:

Number & ""

If you need to use that number in later calculations, wrap VALUE() around the formula field reference that creates the string. Here’s one that simply doubles the value, then converts it back into a string:

(VALUE(Formula) * 2) & ""

Does this help?


Thanks so much–that is a super useful formula that will come in handy. It is definitely better than being stuck with a uniform decimal precision, though… removing the trailing 0s is really only helpful when they’re added by Airtable based on the ‘number field’ precision setting.
When the trailing 0(s) represent part of an actual weight measurement I don’t want them to be stripped off because they convey the level of precision of the measurement and removing them would imply a less precise measurement than it actually is.

For instance, I have one scale that accurately measures to +/- 0.001g but can only handle a maximum weight of 200g and another scale that can measure up to many kilograms, but only to a precision of +/-0.1g… so for small samples (up to 250g) my sample data should have 3 decimals of precision (even if some of those digits happen to be 0), but larger samples should only have 1 decimal of precision at the gram scale.

So, by way of example, what I am trying to find is a way for both of these situations to be accommodated within a single data field:

  1. a record entered as 25.100 to stay as is (not to have the trailing 0s stripped to become 25.1 and thus underrepresent the actual precision of the measurement)
  2. a record entered as 250.1 to stay as is (not to have trailing zeros added to become something like 250.100 which implies overstated measurement precision)
    and,
  3. to be able to use the data above in numerical formulas.

I have a suspicion there are probably some workarounds possible if I keep the original data recorded as a ‘Short Line Text’ field and convert to numbers and keep track of decimal points for each record in each formula I use… but that is starting to feel way too complicated and clumsy, and I really hope there is a more direct way to handle this issue. I figured there had to be a simple solution that I was just missing because I can’t imagine this is a particularly uncommon situation. I’m really trying hard to keep all my data and analysis in Airtable, but it is hard to resist the urge to pull the little headache sections like this back over to Excel and undermine the whole streamlining effort.

Thanks so much for the 0 stripping trick and for any other tips you might have up your sleeve!
Best,
-D

Justin_Barrett
Forum|alt.badge.img+21

Thanks so much–that is a super useful formula that will come in handy. It is definitely better than being stuck with a uniform decimal precision, though… removing the trailing 0s is really only helpful when they’re added by Airtable based on the ‘number field’ precision setting.
When the trailing 0(s) represent part of an actual weight measurement I don’t want them to be stripped off because they convey the level of precision of the measurement and removing them would imply a less precise measurement than it actually is.

For instance, I have one scale that accurately measures to +/- 0.001g but can only handle a maximum weight of 200g and another scale that can measure up to many kilograms, but only to a precision of +/-0.1g… so for small samples (up to 250g) my sample data should have 3 decimals of precision (even if some of those digits happen to be 0), but larger samples should only have 1 decimal of precision at the gram scale.

So, by way of example, what I am trying to find is a way for both of these situations to be accommodated within a single data field:

  1. a record entered as 25.100 to stay as is (not to have the trailing 0s stripped to become 25.1 and thus underrepresent the actual precision of the measurement)
  2. a record entered as 250.1 to stay as is (not to have trailing zeros added to become something like 250.100 which implies overstated measurement precision)
    and,
  3. to be able to use the data above in numerical formulas.

I have a suspicion there are probably some workarounds possible if I keep the original data recorded as a ‘Short Line Text’ field and convert to numbers and keep track of decimal points for each record in each formula I use… but that is starting to feel way too complicated and clumsy, and I really hope there is a more direct way to handle this issue. I figured there had to be a simple solution that I was just missing because I can’t imagine this is a particularly uncommon situation. I’m really trying hard to keep all my data and analysis in Airtable, but it is hard to resist the urge to pull the little headache sections like this back over to Excel and undermine the whole streamlining effort.

Thanks so much for the 0 stripping trick and for any other tips you might have up your sleeve!
Best,
-D


I’m not sure what about a Single Line Text field would be either complicated or clumsy. The data you enter will remain exactly as entered: 25.100 stays 25.100, and 250.1 stays 250.1. Yes, it will require a bit more out of any formulas that reference the data, but not that much.

To turn the entered text into a value in a formula, wrap VALUE() around the field reference as I mentioned above. If you need to maintain precision through a calculation, that’s also doable. I’m working my way through a solution for that now, and will post details when that’s done.

Justin_Barrett
Forum|alt.badge.img+21
  • Justin_Barrett
  • Inspiring
  • Answer
  • December 24, 2019

Okay, here’s the solution I came up with. Let me know how this works for your situation.

{Number} is a Single Line Text field so that I can set any precision that I want, with that precision calculated in the {Precision} field:

LEN(Number) - FIND(".", Number)

The {Double} field formula is the pretty much the same as last time, outputting a string.

(VALUE(Number) * 2) & ""

I then use that in {Double Display} to re-pad the necessary number of trailing zeroes to match the precision of the original.

IF(
    Number,
    Double &
    IF(
        NOT(FIND(".", Double)),
        "."
    ) &
    REPT(
        "0",
        Precision
        + (FIND(".", Double) = 0)
        - LEN(MID(Double, FIND(".", Double) + 1, Precision))
    )
)

When using this system yourself, you’d do something similar: have one formula that performs the actual calculation(s) you want and outputs a string, and another that reformats the result to match the original value’s precision.

    D
    • Daniel_MacPhee
    • Author
    • New Participant
    • December 26, 2019

    Thank you SO MUCH for all your work to help sort out a solution to my nit-picky problem! This approach works out great. Your expertise is so helpful, and the extra effort you so generously put in to work it through and explain step by step is SO greatly appreciated. You’ve solved my problem and taught me a ton in the process. Thanks again!

    It is a shame that it takes such a convoluted work-around to effectively undo Airtable’s convenience feature of pre-specifying a number field’s decimal precision. If only there was a standard option to turn that feature on or off… :winking_face:

    Thanks again for all your insight and patient guidance,
    D

      M
      Forum|alt.badge.img+20
      • M_k
      • Inspiring
      • December 26, 2019

      Hi @Daniel_MacPhee and @Justin_Barrett

      I thought that Justin should see your wonderful reply, so I placed an ampersand (@) in front of his name, so he will see it for sure.

      Mary K

        D
        • Daniel_MacPhee
        • Author
        • New Participant
        • December 26, 2019

        Hi @Daniel_MacPhee and @Justin_Barrett

        I thought that Justin should see your wonderful reply, so I placed an ampersand (@) in front of his name, so he will see it for sure.

        Mary K


        thanks @M_k :slightly_smiling_face: :thumbs_up:

          Justin_Barrett
          Forum|alt.badge.img+21

          Thank you SO MUCH for all your work to help sort out a solution to my nit-picky problem! This approach works out great. Your expertise is so helpful, and the extra effort you so generously put in to work it through and explain step by step is SO greatly appreciated. You’ve solved my problem and taught me a ton in the process. Thanks again!

          It is a shame that it takes such a convoluted work-around to effectively undo Airtable’s convenience feature of pre-specifying a number field’s decimal precision. If only there was a standard option to turn that feature on or off… :winking_face:

          Thanks again for all your insight and patient guidance,
          D


          Glad to hear that it works for you! :slightly_smiling_face: As for a standard option, I suggest posting in the Product Suggestions category (after a quick search to see if such a suggestion already exists).

            Terms & ConditionsAccessibility statement