Conditional Rounding of Numbers, or Precision based on Value

[Ben_Simms]

Hi there,

I’m trying to replicate some spreadsheet conditional formatting for some data that I require publishing with some specific rules around precision - I can’t seem to replicate this using the functions that are available in Airtable (or so I think).

Has anyone found a way to display numbers in formats like this:

Rules:
If < 1, then 2 decimal places.
If < 10, then 1 decimal place,
If >= 10, then 0 decimal places.

So in theory I would get the following numbers:
0.1623 -> 0.16
0.2688 -> 0.27
1.2344 -> 1.2
3.3759 -> 3.4
12.6984 -> 13

etc.

I have tried using ROUND(Value,if(value<10,1,0))

But as this ultimately depends on the precision setting this doesn’t display what I need.

Is there a way to recreate an excel function;
=TEXT(Value,if(Value<10,“0.0”,“0”))

Any tips would be appreciated!

[Tuur]

Maybe you can turn it around by using three if statements with round inside 'm & adding these up?

[Ben_Simms]

Thanks for the suggestion - I’ve just tried that but comes up against the same problem with the other way around - the precision factor is set by the field and I can’t override.

Thanks though :slightly_smiling_face:

[Tuur]

Ah. I see.

Apparently you can only do this if you don’t mind converting the numbers to text. If you add:

& "" 

somewhere in the formula, the field won’t be formatted as a number.

[Ben_Simms]

Hmm, interesting that may work!

Edit: Just tried it - this works perfectly. Great suggestion, thanks

[Tuur]

My pleasure. :slightly_smiling_face: