Bug 85551 - LO Calc and MS Excel treat invalid OFFSET height and width parameters differently
Summary: LO Calc and MS Excel treat invalid OFFSET height and width parameters differe...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:7.1.0 target:7.0.0.1
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2014-10-28 12:22 UTC by Evgeny Chesnokov
Modified: 2020-07-01 10:42 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
A sample reproducing and illustrating the issue. (10.01 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-10-28 12:22 UTC, Evgeny Chesnokov
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Evgeny Chesnokov 2014-10-28 12:22:25 UTC
Created attachment 108569 [details]
A sample reproducing and illustrating the issue.

Problem description: 

The issue is caused by the difference in how MS and LO treat the INVALID formula in the customer's file.
There is this OFFSET() method. It creates a cell range using the provided origin cell, offset for this cell and a height and a width of the required cell range. Height and width parameters are to be positive values as stated in the method's specification in the MS Excel help file. Yet customer uses negative value for the width parameter.
In MS Excel, negative value is honored no matter what documentation says: you get a cell range going from the reference cell and to the right, if the provided width value is negative.
In LO it is just set to default value (1), causing the difference in calculation.

This results in different calculation results.

Steps to reproduce:
0. open a clean worksheet
1. create a vertical list of numbers
3. in a separate cell, enter the formula for =SUM(OFFSET()), where OFFSET is provided with a reference cell of the END of the list of numbers and a NEGATIVE cell range height.

Current behavior:
Sum will return 0, as the negative height is substituted with a default 1.

Expected behavior:
Sum of the numbers in the created list.
              
This behaviour is clearly illustrated in the attached document.
Comment 1 Evgeny Chesnokov 2014-10-31 04:55:33 UTC
> and to the right
to the left, of course. Typo.
Comment 2 raal 2014-10-31 13:44:23 UTC
According to https://support.microsoft.com/kb/184109/en-us  this behaviour is bug in excel. Setting as NEW because we should trigger error.
Comment 3 QA Administrators 2015-12-20 16:17:15 UTC Comment hidden (obsolete)
Comment 4 QA Administrators 2017-01-03 19:49:44 UTC Comment hidden (obsolete)
Comment 5 Eike Rathke 2020-06-30 18:45:46 UTC
That MS support page meanwhile gives 404.

What does a current Excel actually do for negative height/width arguments? I'd presume error because https://support.office.com/en-US/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66 says "must be a positive number" for both. 

That is congruent with ODFF https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#OFFSET "Constraints: NewWidth > 0; NewHeight > 0".

So we should raise an error.
Comment 6 Commit Notification 2020-07-01 00:50:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/af9ed183399d68c42a66382ecf32f402403340ff

Resolves: tdf#85551 OFFSET() NewWidth and NewHeight must be >0 if given

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2020-07-01 10:42:14 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-7-0":

https://git.libreoffice.org/core/commit/5eb23da4c4a6ba6c35142918d0313432365b8e51

Resolves: tdf#85551 OFFSET() NewWidth and NewHeight must be >0 if given

It will be available in 7.0.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.