Formatting columns with different decimal places

[Lochlann_Toolin]

I want to show different row with different decimal places.

I have a column with the number of decimal places in each row

For Example, One row maight have a value of 12,456.54 and for this i only want 2 decimal places but another row has 1.2548 and for this row i would want 4 Decimal.

I tried to create a virtual column using the Decimal place from each row but i cannot format it. As it seems you are only allowed to pick the decimal digits for the entire table 

[1minManager]

Decimal places are set at the column level not the table so you could have:

Column A = 1234.5678

Column B = 1234.56

What you can't have is different decimal places in the same column.  So

If you put 1234.56 in Column A then you'd get 1234.5600

If you put 1234.5678 in Column A then you'd get 1234.57

You're only other option would be to set a column as a text column.  Then both "1234.5678" and "1234.56" would be fine.  Though you wouldn't be able to do any maths formulas on them

Simon@1minManager.com

[WillowMobileSys]

Correct.  The decimal digits are applied to the column.  There is not a way to provide an expression to tailor it to each row.  However, if you do NOT specify any decimal digits (and if I remember correctly) the column will only display the significant digits.  Eg.  12,456.54000 will display as 12,456.54  while 1.254800 will display as 1.2548.

EDITED:  I just tried to confirm what I said above.  It seems when a column is defined as Decimal is defaults to 2 decimal places.  And afterwards must have a value.  Maybe I remembered incorrectly?  As @1minManager suggested by using a TEXT column and as I said below with expressions you can control the shown value.  And actually there is a DECIMAL() function that will allow the text values to be used in an expression mathematically.

If you have certain value types that you want to be sure only reflect 2 decimal digits even when there are more significant digits in the value, then, I believe,  you can apply expressions to modify the values as needed.  Sometimes, it is not straight forward but can be done.  How/Where you apply the expression will depend on your data structure

[Lochlann_Toolin]

Thanks for the replies

This is for financial numbers and the default for large numbers is two decimal places and for smaller numbers it is 4 decimal places. The problem is that i have view these prices for years on financial screens and it helps to keep the number of decimal place stable for fast viewing, if a number that is usually 1.0456 (USDEUR) and it goes to 1.0400 is is unnerving to see 1.04 and not 1.0400. Plus it means having to break up the input into many many different tables rather than just have one GOOGLEFINANCE table. is there a way to make it text but then have the ability to set the decimal place per ticker? rather than many different tables?

[WillowMobileSys]

Do you only have the 2 versus 4 decimal place "versions" to deal with or are there others?

[Lochlann_Toolin]

Just the two, either 2 or 4 places, is there an IF THEn statement using text and a format I could use?

[WillowMobileSys]

I'm concerned that simply stuffing the decimal into a TEXT column, might pose issues when attempting to retrieve the Decimal representation for calculations.

I was thinking of an alternative solution where your app includes TWO Decimal columns - one formatted to 2 places and the other formatted to 4 places.  You can then use some criteria to decide which column holds the value for the row. For best user experience, you would want to show them in a single column and would need a n additional TEXT column anyway to handle that - for display only. 

The benefit is that you retain the raw Decimal values you can use in calculations. The drawback is that you will need decision logic everywhere you need to use the Decimal value to determine which Decimal column is used.

[Lochlann_Toolin]

Thank you so much, i like that idea.

Let me play around with that concept and see what happens. I will let you know. Again thanks for the great idea

[Lochlann_Toolin]

Thanks, this sort of worked but

1. Because it is text i cannot get it to Align on the right hand side

2. I have lost the comma on the thousand as it is now text not a number

3. despite the system saying it has 4 decimal ditits it will not show the final zero if the digits are less than 4

Is AppSheet a text based system? I seem to have a lot of problems using numbers compared to how easy it is in Google sheets?

[WillowMobileSys]

1) Correct there would be an alignment difference.  If it is the difference in alignment, you can turn off the right-alignment of the numeric columns - at least in Table views which is likely where this is an issue.  Go the UX->Options->Table View settings to find this option.

2) When you move the Decimal in the Text column, use the TEXT() function.  It will retain the numeric formatting.

3)  If you are referring the display in the Text column after the value is moved,  the TEXT() function will correct for this (see image below).  If you are referring to the column defined as Decimal, make sure you have set the Decimal Digits value to 4 instead of the Numeric Digits setting  ( I made this mistake earlier)