Bug 119033 - ADDRESS function >> result of calculation is displayed instead of #VALUE! error if "ABS" argument is value in range from 5 to 8
Summary: ADDRESS function >> result of calculation is displayed instead of #VALUE! err...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: low trivial
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2018-08-01 10:54 UTC by Andrey
Modified: 2019-08-19 11:52 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

sample file (8.21 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-08-02 09:54 UTC, Xisco Faulí

Note You need to log in before you can comment on or make changes to this bug.
Description Andrey 2018-08-01 10:54:02 UTC
According to specification: Abs determines the type of reference:
1: absolute ($A$1)
2: row reference type is absolute; column reference is relative (A$1)
3: row (relative); column (absolute) ($A1)
4: relative (A1)

It should not give result in case, when abs=5..8

Steps to Reproduce:
1. Run LibreOffice calc
2. Enter following formulas to any cell:
=ADDRESS(1, 1, 5)
=ADDRESS(1, 1, 6)
=ADDRESS(1, 1, 7)
=ADDRESS(1, 1, 8)
3. Apply and observe result

Actual Results:
Calculation result is displayed:
abs=5 - absolute ($A$1)
abs=6 - row reference type is absolute; column reference is relative (A$1)
abs=7 - row (relative); column (absolute) ($A1)
abs=8 - relative (A1)

Expected Results:
#VALUE! error

Reproducible: Always

User Profile Reset: Yes

OpenGL enabled: Yes

Additional Info:
Version: (x64)
Build ID: dc68ad6c5cde3c62874b96422f2e5e8252499bad
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: en-US (en_US); Calc: CL
Comment 1 m.a.riosv 2018-08-01 20:54:59 UTC
Repro with
Version: (x64)
Build ID: c20eb29560fa9d9e552c799203156c0742451eba
CPU threads: 4; OS: Windows 10.0; UI render: GL; 
TinderBox: Win-x86_64@42, Branch:libreoffice-6-1, Time: 2018-07-28_12:44:37
Locale: es-ES (es_ES); Calc: CL

But looks to me as minor issue.
Comment 2 Xisco Faulí 2018-08-02 09:54:39 UTC
Created attachment 143923 [details]
sample file
Comment 3 Xisco Faulí 2018-08-02 09:55:32 UTC
Also reproduced in

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-

@Winfried, I thought you could be interested in this issue...
Comment 4 Eike Rathke 2018-08-15 09:29:55 UTC
The cases 5,6,7,8 are explicitly handled as synonyms for 1,2,3,4 at https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr1.cxx#8089

I guess that is for some compatibility with another spreadsheet application.

Excel documentation https://support.office.com/en-US/article/ADDRESS-function-D0C26C0D-3991-446B-8DE4-AB46431D4F89 doesn't mention these values either, but maybe it handles them or earlier versions did such thing?

The code is in since the early (then deactivated) prototype of 2006 commit 6aadb208c71286cf4fcbaf7c611c45fd2e0c6c92

FWIW, Gnumeric handles this the same but also documents only 1,2,3,4. (not really a surprise considering the author of the OOo prototype code was Jody ;-)
Comment 5 QA Administrators 2019-08-19 06:59:32 UTC Comment hidden (obsolete)
Comment 6 Eike Rathke 2019-08-19 11:51:52 UTC
No news here. Code is still the same, so.. I suggest to close this as wontfix, no one jumped in on comment 4 and I much believe the code is there for a compatibility reason.