Hello,
What format should be used to achieve the topic as below?
Thank you in advance
By using the format code “00.00” in the applied Cell style.
Thank you for your response but using your solution, where it types 1 it gets 00.01, if it types 11 it gets 00.11 etc.
Format Code 00.00 on a cell having 1 yields 1.00 and not 00.01
Sure - you tell to show 2 decimal places an that happens and you get 111.00 - This is just another representation of the number 111 (content does not change). The same happens with 1 - you get 1.00 and the content doesn’t change.
My default assumption on this English Ask page is (unless the question tells anything else):
decimal separator is: . (dot) - especially if questioners uses that separator.
Regarding the question: Your question seems to be inspired by accounting machines/calculators, which had a functionality to perform currency calculations without the need to enter decimal separators. I can’t imagine that your desired result could be achieved - even not visually - by doing any formatting, since this would requires “changing content by formatting”.
I don’t know how to go about it, but perhaps a good tip would be how I would like it to be used. Assuming the scenario I presented in the content of the notification itself, it could alternatively look like this:
The value in cell 1 and it displays it as 1
The value in cell 11 and it displays it as 11
The value in cell 111 and it displays it as 11.10
The value in cell 1111 and it displays it as 11.11
It requires some conditional calculations. It is not achievable with formatting only.
Thank you for your response. Could you please guide me. I do not know where to start
I don’t see too much regularity in your samples.
Based on your last 4 samples, I can suggest you to use this formula in the adjacent cells:
=IF(A1<100;TEXT(A1;“0”);TEXT(A1/100;“0.00”))
(The results will be strings, but not numbers.)
Or you can apply some Cell Styles conditionally by the formula… Then the numbers stay numeric values for some next calculations.
Is it possible to select a formatting style that simply
This would be something similar like formatting as “Text” but it should be possible to use the number for calculations"
Especially for scientific use cases it is important to keep the number of decimals entered by the user.
e.g. 1.0 is not the same as 1 or 1.00 since the number of decimals tells you about guaranteed precision of the measurements.
(In European numbering format 1,0 is not the same as 1 or 1,00)
To achieve this I had to check if a number has a trailing zero and change the number of decimals for each cell individually to reach the desired result. That is very inefficient especially when numbers are changing frequently, the formatting would have to be changed all the time.
Is it possible to extract formatting code (absolute value, % or, $, € or maybe also the unit e.g. g/mol from a second field/cell containing the unit e.g. directly behind the main cell that contains the value)?
