Bug 62431 - Wrong unit calculation for inherited formatting
Wrong unit calculation for inherited formatting
 Status: RESOLVED NOTABUG None LibreOffice Unclassified Calc (show other bugs) Inherited From OOo All All medium enhancement Not Assigned

 Reported: 2013-03-17 07:19 UTC by Rainer Bielefeld Retired 2015-01-16 20:30 UTC (History) 4 users (show) ilmari.lauhakangas jmadero.dev philipz85 robinson.libreoffice

Attachments
 Rainer Bielefeld Retired 2013-03-17 07:19:07 UTC ```I did my tests with "LibreOffice 3.6.5.2 " German UI/ German Locale [Build-ID: 5b93205] {pull date 2013-01-18} on German WIN7 Home Premium (64bit), but that's all the same for all other versions. Currently LibO does a wrong automatic unit calculation. Introduction: Units have to be integrated into calculation. Examples: You an add 2 Apples + 3 Apples, the result will be 5 apples Aou can't add Apples and Beans, 2 Apples + 3 Beans = 2 Apples + 3 Beans (or may be 5 Apples + Beans) To Calculate the area of a square: 2m * 2m = 4m² To calculate a velocity you divide a distance by a time, 20 km km ----- = 10 ---- 2 h h for Calculations LibO calculation results inherit formatting of parts of calculation. This works correct in Calc for SUM: SUM(2 €;3 €) = 5 € (same correct result for addition 2 € + 3 € = 5 € Division: 2 € / 3 € = 0,66666 without unit This does not work correctly for Multiplications: Current result: 2 € * 3 € = 6 € Should be: = 6 €² This does not work at all for user defined units It would be great to get some more mathematical correctness here.``` Yousuf Philips (jay) (retired) 2014-05-29 01:48:50 UTC `Can you provide a sample document with this happening as i cant create the necessary scenario.` QA Administrators 2015-01-10 18:07:05 UTC ```Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team Message generated on: 10/01/2015``` Yousuf Philips (jay) (retired) 2015-01-12 10:51:25 UTC ```Not a calc expert but believe its only doing simple multiplication and doubt multiplying a currency by another currency value should result in currency square. @Qubit, Jmadero, Beluga: what do you guys think?``` Robinson Tryon (qubit) 2015-01-14 07:33:45 UTC ```(In reply to Jay Philips from comment #1) > Can you provide a sample document with this happening as i cant create the > necessary scenario. Rainer is retired from QA, so if we want a test document, we'll have to create one. (In reply to Jay Philips from comment #3) > Not a calc expert but believe its only doing simple multiplication and doubt > multiplying a currency by another currency value should result in currency > square. Rainer's basic premise is correct: If you multiple X dollars by Y dollars, then you should get XY dollars^2 (And no, I don't have a particular real-world scenario in mind for that). An easier use-case would be Acceleration in m/s^2 or something. I'll try creating a test document``` Robinson Tryon (qubit) 2015-01-14 07:34:30 UTC ```Created attachment 112199 [details] Test spreadsheet for calculations involving units``` Robinson Tryon (qubit) 2015-01-14 07:38:42 UTC ```TESTING with Ubuntu 14.04 + LO 4.4.0.2 (In reply to Rainer Bielefeld Retired from comment #0) > Units have to be integrated into calculation. Examples: > ... > This works correct in Calc for > SUM: SUM(2 €;3 €) = 5 € (same correct result for addition > 2 € + 3 € = 5 € > Division: 2 € / 3 € = 0,66666 without unit > > This does not work correctly for Multiplications: > Current result: 2 € * 3 € = 6 € > Should be: = 6 €² > > This does not work at all for user defined units > > It would be great to get some more mathematical correctness here. REPRO: Check out attachment 112199 [details] for an example of the problem with multiplication of numbers bearing units. CONFIRMED: Multiplication of units is not formatted as expected. Status -> NEW Hardware -> (generalize)``` Yousuf Philips (jay) (retired) 2015-01-14 13:17:48 UTC `Spoke with beluga and matthiew in the IRC about this issue and as "dollars" is a formatting on a cell and when you are doing calculations it is only multiplying the contents in the cell and not its formatting, multiplying "X dollars by Y dollars, ... should get XY dollars^2" is not possible.` Robinson Tryon (qubit) 2015-01-14 17:14:48 UTC ```(In reply to Jay Philips from comment #7) > Spoke with beluga and matthiew in the IRC about this issue and as "dollars" > is a formatting on a cell and when you are doing calculations it is only > multiplying the contents in the cell and not its formatting, Units are obviously more than just an afterthought in the model we're using in Calc. As Rainer noted: > Addition: 2 € + 3 € = 5 € (Retains units) > Division: 2 € / 3 € = 0,66666 (Loses units) So IMHO we should either 1) Do nothing special to propagate units through a calculation, or 2) Try to push units through a calculation completely correctly, or 3) Clearly and fully document the inconsistency in the Calc Manual and Wikihelp under a new section with a name like "Limitations and Bugs" > multiplying "X > dollars by Y dollars, ... should get XY dollars^2" is not possible. It sounds like we'll have to go with (3).``` Yousuf Philips (jay) (retired) 2015-01-15 00:44:12 UTC ```(In reply to Robinson Tryon (qubit) from comment #8) > Units are obviously more than just an afterthought in the model we're using > in Calc. As Rainer noted: > > > Addition: 2 € + 3 € = 5 € (Retains units) > > Division: 2 € / 3 € = 0,66666 (Loses units) \$ 2 / \$ 3 = \$ 0.66666 if you set the cell formatting of 0.66666 to number format. In attachment 112199 [details], C3 is '0.5' and not '\$0.50' because you havent set the cell formatting as 'number' which you did in C2 and C4. Am i missing something?``` Robinson Tryon (qubit) 2015-01-15 01:16:17 UTC ```(In reply to Jay Philips from comment #9) > (In reply to Robinson Tryon (qubit) from comment #8) > > Units are obviously more than just an afterthought in the model we're using > > in Calc. As Rainer noted: > > > > > Addition: 2 € + 3 € = 5 € (Retains units) > > > Division: 2 € / 3 € = 0,66666 (Loses units) > > \$ 2 / \$ 3 = \$ 0.66666 if you set the cell formatting of 0.66666 to number > format. > > In attachment 112199 [details], C3 is '0.5' and not '\$0.50' because you > havent set the cell formatting as 'number' which you did in C2 and C4. Am i > missing something? IIRC, I only set the formatting on the data source cells in column A. The formatting of the cells in column C was automatically decided by the result of my computations and (I assume) LibreOffice pushing units.``` Markus Mohrhard 2015-01-16 19:42:14 UTC ```This bug fixes number formats with units. We don't have units which would support stuff like km/h, ... and instead all that we have is number formats which specifies how to represent a number. That some number formats (if none is set yet) is determined by the formula interpreter is a feature to make it easier. Here is no bug. The plan to introduce an unit system has been discussed and there exist some plans but for the current number formats this is clearly not a bug.``` Yousuf Philips (jay) (retired) 2015-01-16 20:17:51 UTC ```(In reply to Robinson Tryon (qubit) from comment #10) > IIRC, I only set the formatting on the data source cells in column A. The > formatting of the cells in column C was automatically decided by the result > of my computations and (I assume) LibreOffice pushing units. You are correct here. With the division operator, it doesnt transfer the cell formatting to the cell.``` Robinson Tryon (qubit) 2015-01-16 20:30:31 UTC ```(In reply to Markus Mohrhard from comment #11) > This bug fixes number formats with units. We don't have units which would > support stuff like km/h, ... and instead all that we have is number formats > which specifies how to represent a number. > > That some number formats (if none is set yet) is determined by the formula > interpreter is a feature to make it easier. > > Here is no bug. The plan to introduce an unit system has been discussed and > there exist some plans but for the current number formats this is clearly > not a bug. What's our documentation like of the current behavior? I don't see anything in the latest Calc manual: https://wiki.documentfoundation.org/images/4/47/CG41-CalcGuideLO.pdf```