Description: For certain combinations of font, column width, zoom factor and number value and without specifying decimal places in the format, ### is displayed instead of a rounded value that would fit in the cell. With the number value =247/210 occurred with: Liberation Sans 10pt, 2.28cm, 90% Times New Roman 10pt, 1.85cm, 100% Steps to Reproduce: 1. Font, column width and zoom as in description 2. enter calculated value =247/210 3. change zoom factor to see what should happen Actual Results: ### Expected Results: 1.17619048 resp. 1.1761904762 Reproducible: Always User Profile Reset: Yes Additional Info: Maybe round to one less place so that the result fits into the cell. Workaround: different font, zoom factor or column width Version: 24.2.4.2 (X86_64) / LibreOffice Community Build ID: 51a6219feb6075d9a4c46691dcfe0cd9c4fff3c2 CPU threads: 8; OS: Windows 10.0 Build 22631; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: de-DE Calc: threaded
Created attachment 195192 [details] Screenshot of the issue
Instead: 1. Select the relevant cell(s) 2. Format cell ([CTRL]+[1]) 3. Alignment 4. Shrink to fit cell size. IMO, -1 for this request.
Displays too many digits and makes the font unreadably small.
If it always happened, it wouldn't be a bug. But as it is, it's a calculation error when calculating the font width.
Typical would be: a mix of float and double.
What's the original cell number format? For example, let's say you have a fixed amount of decimals: 0.0000 ...or some other number format ("0.????", or scientific, or currency, or fractions, or...). Are you suggesting that the displayed format should change according to zoom factor, or font size, or font type, or...? There are several ways to avoid the "###". The alignment (as in comment 2) is one of them – just set an adequate column width. You could also add a padding (in the borders tab of cell format). What about setting an "Optimal width" for the cell/column (which is not perfect and still has issues under certain circumstances) using an adequate zoom level? The behavior varies, depending on the starting column width and other settings.
The cell format is "Decimal Standard". The displayed format should be independent of font, zoom factor and cell size. The behavior should be consistent. Either always display ### if the unrounded value does not fit in the cell - bad solution. Or round so that the value fits in the cell, in the specified font, zoom factor and cell width. At the moment it is like this: with one font, at a zoom factor of 90% and a cell width of 1.85 cm, it is rounded, and at a zoom factor of 100%, ### is displayed. With the other font and cell width of 2.28, it is rounded at 100%, and at 90%, ### is displayed. That seems wrong to me.
All has nothing to do with tehe format. Type =247/210 in A1 Will show a rounded value. You could set column width without any problem. Will show rounded values. Now zoom step by step. 90% and 60% gives ### Other zoom levels won't be attached. This bug appears in Version: 24.2.4.2 (X86_64) / LibreOffice Community Build ID: 51a6219feb6075d9a4c46691dcfe0cd9c4fff3c2 CPU threads: 6; OS: Linux 6.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: de-DE Calc: threaded This bug won't appear in LO 7.4.7.2 on the same system. Never will be shown ### in LO 7.4.7.2. So a regression.
Bug appears on different zoom levels. With LO 24801 it won't appear on 90%, but will appear on 60%. Wont appear on Version: 7.6.4.1 (X86_64) / LibreOffice Community Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1 CPU threads: 6; OS: Linux 6.4; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: de-DE Calc: threaded Will appear first here with LO 24.2.0.3. I will set this version to the earliest version.
JIC, let's clarify the report. "Real" rounding values (by cell format, round() function or similar) is unrelated. There are several reports about some kind of "inconsistency" in displaying either "###" or a value, with some modified format (e.g. cell alignment) when changing zoom levels. Some reports are related to some patch that modified the behavior in some way. Some reports are related to the font's characteristics (e.g. fixed-width/mono-spaced vs proportional). This report might or might not be a dupe of any of those. I have to point out one relevant item... While changing zoom levels, if a numeric value is displayed at some zoom level, and then "###" is displayed at a higher zoom level (i.e. zooming-in), and finally a numeric value is displayed again at an even higher zoom level, then that's something that might be worth improving. But... If the "###" is displayed (instead of the expected value) when zooming-out, and then with lower zoom levels the value is never seen again (so, always the "###" is seen starting from some zoom level, and zooming out from that point), then there is no bug but a simple natural consequence of zooming out. Anyway, the reported behavior might be a consequence of how many decimals are used while displaying the cell value, the width of the column, the amount of pixels for the column's width at a certain screen resolution and zoom level, and the horizontal size of the specific characters (e.g. numbers) of the specific font/size combination. Not being a developer myself, IDK whether it is possible to adjust the algorithm for "any-and-every" font simultaneously.
Default font here for LibreOffice is "Liberation Sans 10 pt". When zooming from 100% down to 50%: 100% numbers are shown 90% ◄ ### is shown 80%, 75% and 65% numbers are shown 60%, 55% and 50% ◄ ### is shown 45% numbers are shown When zooming from 100% up there will also be shown ◄ ### is shown at level 120%, 160% When changing the font (here to DejaVu Sans) there are other levels, which won't work. Zooming up seems not be touched for this font. Numbers were shown every time. Bibisecting should start with simple =247/210 in cell A1 Font "Liberation Sans 10pt". → and then zooming down…
(In reply to Robert Großkopf from comment #11) FWIW, my experience with LO 24.2 and 24.8beta1 on Windows is different than what you report. Actually, my experience is even different between these 2 branches. Moreover, the amount of decimals displayed under each condition (e.g. zoom level) varies, and it is not always "more decimals displayed when zooming-in", nor the opposite. I assume it varies according to some algorithm, which is what would need to be changed in order to improve the resulting behavior. Probably "improving" under certain conditions would make it worse for other situations. > When changing the font (here to DejaVu Sans) there are other levels, which > won't work. Zooming up seems not be touched for this font. Numbers were > shown every time. Which confirms what I posted in comment 10. > Bibisecting should start with simple > =247/210 in cell A1 Why exactly? What if the specific numbers give you one behavior, and testing with different characters (which, generally speaking, might imply different widths for each glyph using a non-mono-spaced font) shows a different behavior? Some proportional fonts would show the numbers _almost_ as mono-spaced, but it is not a rule at all. What if you tried (each on different columns): =1/9 =1/3 =1/7 =8/9 ...respectively, and with different fonts and font sizes (again, each on different columns)? And what happens when changing the cell format, instead of using General/Standard? AFAIK, the only way to make sure that the "###" is never displayed is to use the "Shrink to fit cell size" alignment. I would suggest simply using it. As a side note, alignment of numbers (e.g. for accounting) is _much_ more important than whether "###" is shown under some zoom level.
I suspect the error is in the methods GetMaxDigitWidth, GetDotWidth, (for numbers with a sign or exponent also GetSignWidth and GetExpWidth) on the one hand and GetFmtTextWidth on the other hand. If the latter, under certain circumstances, results in a larger width than the sum of the others, that would be a reason for the error.
(In reply to ady from comment #12) > (In reply to Robert Großkopf from comment #11) > > AFAIK, the only way to make sure that the "###" is never displayed is to use > the "Shrink to fit cell size" alignment. I would suggest simply using it. Please have a look at the version, where this bug won't happen without using "Shrink to fit cell size". It is a new bug since LO 24.2, it won't happen with LO 7.6. Only solution would be: Find the commit, which is the last commit before the bug appears. This commit is the reason for this bug.
It could also be that the commit uses a method that was not used before, but was already broken. In this case, it would be important to find the error there, because this method may cause an unknown error somewhere else. This could also be identified once the "guilty" commit has been found.
The following error probably has the same cause: with a cell width of 2.24 cm and the font Liberation Sans 10pt, the value 9.9999999991 is displayed as 10, although the rounded value 9.999999999 fits in the cell.
(In reply to Pit Hauge from comment #16) > The following error probably has the same cause: with a cell width of 2.24 > cm and the font Liberation Sans 10pt, the value 9.9999999991 is displayed as > 10, although the rounded value 9.999999999 fits in the cell. While the displayed result varies with column width and zoom level, what you are mentioning in comment 16 should not be considered the same exact "cause". Please note that much-older versions behave in the same way regarding that "rounding", whereas the behavior that Robert reproduced starts in the 24.2 branch and it is about showing "###". Please remember that one of the factors (for _everything_ related to how the cell is displayed) is column width. With enough width, there is no issue.
I wrote "probably" because in this case too, a possible explanation would be that the count of decimals to which the number should be rounded is determined incorrectly. 9.9999999991 rounded to nine decimal places is 9.999999999. Rounded to eight decimal places, it is 10.
Rounding causes errors, which are smaller when there are many decimal places, but the principle is the same. Whether you round decimal or binary does not change the principle. Therefore, the following simplified example calculation: let the width of a digit be 100/3 units, rounded to 33; the width of the decimal point be 100/10 units, rounded to 10; the cell be 109 units wide. This determines that GetOutputString can output four characters: one place before the decimal point, one decimal point, two places after the decimal point. The three digits result in a width of 3*100/3 = 100 units, plus the decimal point with 100/10 = 10 units, making a total of 110 units, i.e. more than the width of 109 units. That is why ### is displayed.
Regression started from the following commit, bibisected using repo bibisect-linux-64-24.2. Adding CC: to خالد حسني (Khaled Hosny). https://git.libreoffice.org/core/+/4b743de97fc133623e46827869c4ea3eb845ad47%5E! Author: Khaled Hosny <khaled@libreoffice.org> AuthorDate: Mon Jul 17 12:38:41 2023 +0300 Commit: خالد حسني <khaled@libreoffice.org> CommitDate: Sun Jul 23 06:01:56 2023 +0200 tdf#156234: Don’t round glyph coordinates when doing subpixel positioning
(In reply to Aron Budea from comment #20) > https://git.libreoffice.org/core/+/ > 4b743de97fc133623e46827869c4ea3eb845ad47%5E! Doesn't lead to the correct commit, let's try this instead: https://git.libreoffice.org/core/commit/4b743de97fc133623e46827869c4ea3eb845ad4
Since both nWidth and nMaxDigit are rounded, the result nNumDigits, which should better be called nNumChars, is based on incorrect assumptions. So either a tolerance must be built into the comparison "(nActualTextWidth > nWidth)" or the test must be repeated with a smaller nNumDigits.
I am not a developer and I apologize for the intrusion, but this bug has affected all of my spreadsheets and those of many of my friends. Even a simple calculation like "=22/7" gives "###". My request is for its priority to be increased, if possible and reasonable. Thank you in advance!
(In reply to Aron Budea from comment #20) > Regression started from the following commit, bibisected using repo > bibisect-linux-64-24.2. Adding CC: to خالد حسني (Khaled Hosny). > > https://git.libreoffice.org/core/+/ > 4b743de97fc133623e46827869c4ea3eb845ad47%5E! > Author: Khaled Hosny <khaled@libreoffice.org> > AuthorDate: Mon Jul 17 12:38:41 2023 +0300 > Commit: خالد حسني <khaled@libreoffice.org> > CommitDate: Sun Jul 23 06:01:56 2023 +0200 > > tdf#156234: Don’t round glyph coordinates when doing subpixel positioning I no longer do any libreoffice development and I can't help much here.
The cause of the error has now been identified; it is a comparison of a sum of rounded values with a rounded sum of float values. I believe I can provide a fix. It only concerns the output2.cxx file. But since I don't have a suitable development environment, I can't even compile it. Who can I send the output2.cxx (revision 8b23abac) - OpenGrok cross reference for /core/sc/source/ui/view/output2.cxx file to so that they can check it and incorporate it into the project? My version is based on revision 8b23abac.