For entering numeric data with decimal places, should have an option to keep set the number of decimal places as per the precision input by the user; not automatically remove any trailing zeros. For example if one enters 0.10, Libre office will truncate to 0.1 and the user has to manually change the number of decimal places. It would be useful if Libre office could keep the number of decimal places based on the number of trailing zeros entered. Another related enhancement would be to have the option to display a specified number of significant figures instead of number of decimal places. And carry it forward in calculations. For example one may input a list of data (ex 120, 34.567, 0.890) and want to display all of them with the same number of significant figures (say 3). This would require changing the number of decimal places for each cell which is impractical for large datasets. This could be extended to calculations where adding or multiplying figures would output a value with the correct number of significant figures (ex 0.10 * 10. = 1.0) These enhancements would be incredibly useful for scientific data where significant figures are important and need to be tracked. Operating System: All Version: 4.1.3.2 release
Hi DF, If you want an enhancement please change the importance from normal to enhancement. Seems there are several request of enhancement, please do one report bug for every request.
Split significant figures part as bug# 77951.
Please could you describe a use-case where it is important to keep the trailing zeros ? Set status to NEEDINFO. Please set it back to UNCONFIRMED once you provided the requested informations. Thank you for your understanding. Best regards. JBF
With most scientific data significant figures are very important. The number of trailing zeros are used as placeholders denoting the precision of the number. https://en.wikipedia.org/wiki/Significant_figures privides a more indepth explanation. Another example would be currency where one might include two trailing zeros after the decimal place even for whole values eg $1.00. This feature request is simply for steamlining the procedure for specifying the precision. Currently one must manually right-click -> format cell -> change decimal places. It could be set automatically based on the number of decimal places entered by the user. Example if I type or paste in 1.00, it should remain as 1.00, not automatically be set to 1.
Many users want to type the fewest possible characters, for example type 1 and get 1.00 by formatting the cells with 2 decimal digits. Best regards. JBF
Yes, that is also useful. Ideally it would be easy to switch between the two.
Perfectly reasonable request (ie. do not alter user inputed numerical value). From what I can see, there is no way to currently do this. For instance: If you want A1 = 2.10 and B1 = 2.100 There is no setting that can apply to both A1 and B1 that would allow this. One option is to add an auto correct option that ignores decimal replacements. New Enhancement Low - currently it seems like a very corner case for when this would be needed. You could always be MORE precise than you need to by making a format something like #.0000000000000000000000000000000000000000 and that should cover everything. (Obviously exagerating there, you could make the 0 to the largest number of trailing zeroes in your data set and then everything else would just be "more precise" than really needed but that should be fine)
Just to add my voice, I'd say this would be a very nice feature to have and it should be an option, it is something I googled for and expected to find.
Please don't change the top section - it's fine to say you're interested in it too but "affects me too" is not a reason to increase the importance. Thanks
I'd agree adding greater precision is usually better than less, but in the case of at least scientific research the correct number of significant figures is quite important. The other examples such as currency I mentioned earlier, or maybe entering a product number which contains a decimal place and has lots of trailing zeros. Ya, I think just being able to disable the auto-correct option for removing trailing zeros would be ideal. Infact the only reason I can think of for that auto-correct feature is strictly aesthetics. And how many people enter trailing zeros that they want auto-removed? In my experience it's be more of a burden than a benefit. So I'd almost suspect disabling that auto-correct feature by default would be more useful for most people. Although this isn't a super critical enhancement, I think this would be too difficult to implement and for those who it would benefit it would make this software more streamlined to use and thus stand out that much more. So I'd suggest boosting the importance of it.
supporting the request, '7,5' (decimal) entered is not! the same as '7,5000' (decimal) entered! 7,5 is a placeholder for the range from 7,450000000000000 to 7,549999999999999 if we stay with 16 digit precision, 7,5000 is a placeholder for the range 7,499950000000000 to 7,500049999999999, it carries some precision information in it which is thrown away when truncating the trailing zeros, additional: user sometimes want a formatted display of columns, without first having to manipulate the cell format, and additional: from such a display respecting the input values you can spot some typing errors which is more difficult with display / values messed up by truncating zeroes and / or overriding cell format, shouldn't be too difficult to implement, an option to automatically change cell format acc. input, and store trailing zeroes in content.xml ...
(In reply to b. from comment #11) > [...] > shouldn't be too difficult to implement [...] Hmmm, not the best way to convince a developer to work on this enhancement. If you know that, why do you don't do that yourself? Rule #1: never tell a developer that a function is easy to implement. Best regards. JBF
@JBF: :-) thanks for the hint, but ... a little late, my time machine is in repair, 'trying myself' ... besides not being a c-programmer i tried to get in touch with LO code ... looks like it will need some more tries ... shortcomings were: - no 'pig picture' what's handeled where, how things should work, - debugger problems to catch 'threads', - too few comments in code to get 'the idea', - no info which other functions might be affected by changes, - 'c' inherently cryptic, thought to leave it to those who learned theese things earlier in live and be myself a bug-spotter, if there would be something like a mentoring service answering questions like 'where is cell format info stored?', 'which module handeles input processing' and plenty! similar i could give it a try ...
Hi ! I just ran into a bug with Calc that looks very much like this one already in discussion. Here are the details I can give to the devs: Formula : =0.76+2.91+3.89+6.34 Output : 13.90 Formula : =0.76+2.91+3.89+6.34 & " $" Output : 13.9 $ In both sequences, the Format Cells / Decimal Places is set to 2. It looks like the adding of the char $ (or any char) would format the decimal numbers close/exact to 1. Libre Office Version : 24.8.4.2 Release On system : Linux Mint 21 Hopefully it gives the devs more info on this matter.
It's not quite the same as this issue. Here it's about keeping the number of decimals displayed the same as the number entered. Eg paste 01.00 and shows up as 01.00 instead of 1 in the cell. Could do this by having a setting in formatting options whereby the format adapts to the data entered. Eg paste 01.00 and would either preserve the formatting as was entered (eg similar to text or WYSIWYG), or set the formatting for that cell as '#0.00'. And/or could paste a list of values along with a format notation either beside or after in a 'format code edit mode' whereby the spreadsheet shows the format codes for each cell. For many cases this doesn't matter as typically a list of numbers will be formatted the same length so can select all data and apply single format code, however this issue is for a list of numbers whereby the number of leading or trailing zeros changes throughout and wants to be preserved (eg for scientific notation). Yet currently this requires manually formatting each individual cell which ranges from tedious to impractical for larger datasets. I had also proposed feature in #77951 https://bugs.documentfoundation.org/show_bug.cgi?id=77951 to add a format option for scientific notation, but could still imagine use cases where this issue still stands. But I would agree your case still seems like a bug, especially since =0.76+2.91+3.89+6.34 &" $" is still treated like a number when referencing that cell in other calculations, so formatting should treat it like a number too. However using format code 0.00 does nothing while @$ returns 13.9 $$ indicating the formatting treats it as text which is inconsistent behavior. What's even weirder is how =TEXT("13.9 $","0.00") returns 13.90 while =TEXT("13.9 $","@$") returns 13.9 indicating formatting that way treats the value as a number. Thus the cell formatting should remain consistent and also treat it like a number. Even better would be ability to format mixed text eg 0.00 @ would turn 13.9 $ to 13.90 $. In your case, one solution is to set the cell with =0.76+2.91+3.89+6.34 and use format code 0.00 "$" or 0.00 \$ or 0.00 $ to get 13.90 $ as per https://help.libreoffice.org/latest/lo/text/shared/01/05020301.html Or set the cell value as =TEXT(0.76+2.91+3.89+6.34,"0.00 $") or =TEXT(0.76+2.91+3.89+6.34,"0.00")&" $" as per https://help.libreoffice.org/latest/en-US/text/scalc/01/04060110.html
brainstorming: - it's not likely that IEEE 754 will change 'normalized' handling for binary datatypes, - it's not likely that Excel, and following suit Calc, will switch to another math engine, - IEEE provides 'decimals saving' for the decimalxxx datatypes, it comes with it's own set of issues and isn't yet well matured, - you can get them in 'gnumeric', as experimental, but AFAIK they stripped trailing zero conservation, - if ... it's really relevant to you there already is an option, enter your input as string, e.g. '0.200 with! the leading apostrophe, you can format that right aligned then looking like a number, and Calc will convert it to 'value' and happily calculate with it when addressed in formulas. - be aware to check for evtl. traps / unexpected behaviour, e.g. low level supporters analysing sheets from others and identifying 'text' by 'left aligned' will like to fail. - be aware that the workaround provides 'input conservation', but no effect on calculation results, - which IEEE 754 decimals try to provide, with very questionable results, e.g. adding 0.200 ( relatively imprecise ) and 0.10000000000 ( higher precision ) results in 0.30000000000, faking a precision which is not! provided by the first operand ( but faking is fashionable since Trump? ). Think we can close this report if the OP is satisfied with the workaround.
(In reply to b. from comment #16) > .... > - if ... it's really relevant to you there already is an option, enter your > input as string, e.g. '0.200 with! the leading apostrophe, you can format > that right aligned then looking like a number, and Calc will convert it to > 'value' and happily calculate with it when addressed in formulas. That is not accurate. https://help.libreoffice.org/25.8/en-US/text/shared/optionen/detailedcalculation.html?System=WIN&DbPAR=CALC&HID=modules/scalc/ui/formulacalculationoptions/dialog-action_area1#@@nowidget@@ > ....
@m.a.riosv, thanks for caring, however before I have to investigate all idiosyncrasies of LO Calc and the plethora of settings ... What is your concern? you think it won't work? consider it locale dependent? consider conversion inaccurate? or other?
Cal is much stricter than Excel about string conversion into numbers. E.g. with functions like SUM() doesn't work.
inconsistencies which c/should evtl. be eliminated? Consistency is valued quite high from the user's perspective. Either a cell qualifies for evaluation as numeric, or not. Sometimes yes and sometimes no is user trapping, also if it's documented somewhere.
Please read the link with care.