Description: The summary says it all. Argument 1: If a number is close enough to zero, to call it zero for currency purposes, the sign of the insignificant digits shouldn't matter. It's not like we already know where a minus sign arises in a series of calculations. For example enter this formula: =23281.51-119.78-23161.73 in one cell and in another cell enter these same values in a different order with this formula: =23281.51-23161.73-119.78 You would expect the signs to be the same when you apply currency formatting to these numbers, no? Argument 2: Let's show some consistency between the "Format as Currency" tool and the "Format as Number" tool. For a number like -.0001 displayed with 2 decimal places, the currency format shows a minus sign while the number format does not. Only when the user adds enough decimal places does the number format change the displayed sign from positive to negative. Argument 3: Let's promote sanity where we can sanely control it. This is just a display formatting choice, folks. If I really care about having zero in that cell because another calculation depends on it, then I will use the ROUND() function. I shouldn't have to obsessively use the ROUND() function to change the display formatting. Steps to Reproduce: 1. Enter -.0001 into a cell. 2. Select the cell containing the number. 3. Click on the "Format as Currency" tool (or Ctrl+Shift+4). Observe the value is displayed as -$0.00 3. Click on the "Format as Number" tool (or Ctrl+Shift+1). Observe the value is displayed as 0.0 without a sign. The minus sign appears when you add enough decimal places. Actual Results: First I asked myself why I still prefer to use LO Calc over Excel. Then tried this same experiment in MS 365 Excel and it's exactly the same result. Even those sums. Then I remembered that we can have the power to make LO Calc better than Excel. Expected Results: I expected LO Calc to be better than Excel. And the currency formatting to work like number formatting regarding sign and precision. Unless there's international standard that says otherwise. Reproducible: Always User Profile Reset: No Additional Info: Version: 6.1.5.2 Build ID: 1:6.1.5-3+deb10u6 CPU threads: 12; OS: Linux 4.19; UI render: default; VCL: x11; Locale: en-CA (en_CA.utf8); Calc: group threaded
The whole request is in fact requesting a change of values by formatting and as a user I'd completely disagree with such approach.
The only approximation it's to set up the option: Menu/Tools/LibreOffiace calc/Calculate - General calculation - Precision as show. So you can avoid the ROUND() function.
(In reply to Uwe Auer from comment #1) > The whole request is in fact requesting a change of values by formatting and > as a user I'd completely disagree with such approach. I agree that a change of values an unreasonable request, which is why I did not request a change of values. I requested a change of formatting to be consistent with the way Format as Number works. The number does not change. Multiplying a small number (displaying as zero due to formatting) by a large number produces a nonzero value when the product becomes larger than a value which rounds to zero. I am just saying to ditch the minus sign when the result displays as zero in currency format with whatever number of decimals you choose.
(In reply to m.a.riosv from comment #2) > The only approximation it's to set up the option: > Menu/Tools/LibreOffiace calc/Calculate - General calculation - Precision as > show. > So you can avoid the ROUND() function. That's an interesting suggestion, but it doesn't work: -.0001 still displays as -$0.00 in currency format. What's more, the proposed cure is worse than the disease. With 2 decimals of calculation precision, apportioning a $10,000.00 expense into parts rounds the parts to the nearest $100.00 And "calculation: precision as shown" is a global setting, not a file setting. This suggestion would be pretty deleterious to a beginner and it's a sneaky way to damage a bunch of financial spreadsheets. Good thing I've got > 40 years professional experience with computers. I can understand this as a misguided attempt to help Engineers work with significant figures, but can someone tell me in which universe this setting would even be desirable to use with currency formatting? I am not trying to avoid using the ROUND() function where it's appropriate. I want calculations to use full machine precision for multiplication and addition, and to round values for display purposes. I think someone did not read the title. It says the format should DISPLAY a -$0.00 value without a minus sign. ROUND() is just a workaround for this display artifact.
re: can someone tell me in which universe "calculation precision as displayed" would even be desirable to use with currency formatting? I might be able to answer my own question. Stock trading platforms carry prices to hundredths of cents so if you want to match the settlement amounts with a spreadsheet then you might want to set the calculation precision to round at 4 decimals, rather than use the ROUND(). Still that's a global setting. I might want that to use that in one or two spreadsheets, but certainly not for all of them unless I happen to be a stock broker.
@Eike, I thought you might be interested in this issue
I reproduce, but I prefer, in your steps to reproduce, the behavior for currency to the behavior for general numbers. -0,00€ shows that the value is not strictly equal to zero, where 0,00 does not. The minus sign make easier to understand why, if you multiply this zero value by 10000, you get a non zero value. Best regards. JBF
(In reply to Jean-Baptiste Faure from comment #7) > I reproduce, but I prefer, in your steps to reproduce, the behavior for > currency to the behavior for general numbers. -0,00€ shows that the value is > not strictly equal to zero, where 0,00 does not. The minus sign make easier > to understand why, if you multiply this zero value by 10000, you get a non > zero value. > > Best regards. JBF A sign debugging tool would be a good undertaking, but to be useful and consistent, positive numbers displaying as 0 should also show their sign as well, i.e. display as +0,00. If I want a debugging tool in my spreadsheet, I prefer that this be a controllable option. Furthermore, do not confound the debugging tool with a display convention for signed numbers. I suggest appending the sign debugging indicator on the right side of 0, so 0- or 0+. If the sign debugging tool is off, just display 0 without a sign as the title suggests, as this might be a report published for public consumption.
The user-visible problem is: having a number like 0.001; formatting it as a number with two decimals, would show "0.00", while formatting is as a currency, would show -$0.00. But the formatting code is completely different. And this is the correct behavior. The format string used for "number with two decimals" is "0.00", which uses a single section (sub-format) for all cases. In this case, the code is able to do the magic - find out how many decimals to show from the subformat, round, find out the sign of the rounded result, and display it. The format string used for "currency" is like "[$$-409]#,##0.00;[RED]-[$$-409]#,##0.00". And it uses two sections: one for positive numbers, and one for negative numbers. Since there are two sub-formats without explicit conditions, the code has to decide which sub-format to use *before* processing the number: because nothing prevents a user to use, say, 10 decimals in negative sub-format, but only 2 decimals in positive. So it checks the sign of the *original* number (not of rounded-to-number-of-decimals); and after that, no rounding result can affect the choice of the sub-format again - it will use the "[RED]-[$$-409]#,##0.00" format string for the rounded result of 0. The format code like "[>-0.005][$$-409]#,##0.00;[RED]-[$$-409]#,##0.00" could do the job. But I think that making such a string as a default is not good. WONTFIX IMO. https://help.libreoffice.org/latest/en-US/text/shared/01/05020301.html
Thank you for reporting the bug. I can confirm that the bug is present in Version: 24.2.1.2 (X86_64) / LibreOffice Community Build ID: db4def46b0453cc22e2d0305797cf981b68ef5ac CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded as well as Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: a2265e8faa099d9652efd12392c2877c2df1d1eb CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded
Back to unconfirmed as wontfix was proposed.