Bug 62431 - Wrong unit calculation for inherited formatting
Summary: Wrong unit calculation for inherited formatting
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-03-17 07:19 UTC by Rainer Bielefeld Retired
Modified: 2015-01-16 20:30 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Test spreadsheet for calculations involving units (11.58 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-01-14 07:34 UTC, Robinson Tryon (qubit)
Details

Note You need to log in before you can comment on or make changes to this bug.
Description 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.
Comment 1 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.
Comment 2 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
Comment 3 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?
Comment 4 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
Comment 5 Robinson Tryon (qubit) 2015-01-14 07:34:30 UTC
Created attachment 112199 [details]
Test spreadsheet for calculations involving units
Comment 6 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)
Comment 7 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.
Comment 8 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).
Comment 9 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?
Comment 10 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.
Comment 11 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.
Comment 12 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.
Comment 13 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