Variable Decimal places on a math column

Hi All

I am trying to work out how to have a math column with variable decimal places, or precision.

As an example, I have a result which is LOAD1 - AFNT1, or 1.000 - 0.998 which would give me -0.002

In this example I have 3 decimal places, and would work fine if I set the precision on the column to 0.000, however this can vary by item.

I have a column that tells me how many decimal places there are (0,1,2,3,4 eg) which is called DECPLACES, so is there a way to tell glides math column how many decimal places to use from this data to calculate the result?

Hoping someone can help with this.

I haven’t tried it dynamically, but I think you can set your math column precision to 4 decimal places, and then do the rounding in your math formula.

Round(LOAD1-AFNT1, DECPLACES)

2 Likes

Hi @Tony_Goodman and welcome :slight_smile:

In addition to @Jeff_Hager’s answer, be also aware there is a difference between the stored result and the displayed result.

With your own example, you could have define the right calculation to get 3 decimals but only let the user see 1 decimal. The stored result would - indeed - be -0.002 but the user would probably see -0.0.

This is what you select in the Display section of the Math column.


Some considerations

If it is your concern to also have a different result displayed, you could go for multiple Math columns and an If → Then → Else one to deal with this display question.

Here is a quick sample, a wonderful Decimals table:


Also this is the set up of some columns for the record:

As you can see, the Precision is set to 1 in Round 8 decimals.
However, it has no consequences when it comes to the final step, the Final result column:

Because the Square root of 2 has no value in Number of decimals, the Else condition is applied, meaning it gets the rounded value to 8 decimals. And it actually displays 8 decimals here :wink:

2 Likes

Thanks so much for this… My only concern here is that there are maybe 30 fields like this on the sheet I am creating, so each one perhaps needs 4 or 5 additional fields just to calculate this result… will this not bloat it?

If you want to avoid having to create 4 or 5 columns for single field (and multiplying that by 30 in your case), there is a more direct approach using a JavaScript column.
It wasn’t mentioned in the previous answer because it’s slightly outside the scope of no-code logic obviously. However I believe it makes sense here.

JavaScript column

Instead of the steps described ealier, siply add a JavaScript column and configure it like this:

Here is the code:

let value = p1;
let decimals = p2;
return value.toFixed(decimals)

It would also work with just return p1.toFixed(p2) - the classic readability versus conciseness dilemma :relieved_face:

This code uses the value of p1 (which corresponds to Value in the screenshot) and returns it with the number of decimals specified in the Number of decimals column - the p2 parameter. Note that when the number of decimals is not filled (e.g. in the third row), the default behaviour is to remove all decimals.


A tip when using JS

Assuming you go down this path, here’s a useful tip:

Since you’ll be applying the same function across 30 columns, ithis approach makes your logic easier to maintain, more scalable and less prone to typos :wink:

Ideally, this Template column should be stored in the Users table - this way , you’ll be able to reuse the same function in any other table within your app.
Very helpful when you’re applying the same logic in multiple areas (I saw this in one of Marco Volpato’s tutorials - brilliant :+1:)


Performances

It’s very difficult to provide feedback on performance-related questions. It depends on many factors, even though there are some generally valid tips - such as avoiding If → Then → Else columns with many conditions.
One thing that’s easy to do, however, is to activate a feature called Analyse Table Performance, available under Settings → Developper Tools


You can now run the analysis on your table and identify possible bottlenecks:



Hope this is what you were looking for! :slight_smile:

Thats great, thank you! I will have a play around with this and report back

Not sure I am entirely getting it (frustratingly, i usually get things quickly!), however I am getting this TypeError when I edit the column… ive tried both ways of doing the JS but it still returns the same, and my results strangely have no decimal places in them regardless of what is in the DecimalPlaces column.

Scrub that, as soon as I posted it I saw the error, DecimalPlaces was just text and not the Column Name… getting somewhere now!

nicolas_joseph, just wanted to say thank you, all working well!

1 Like