Issue 17664

Summary: spreadsheet / calc doesn't import Excel's OFFSET, expecting SUM(OFFSET
Product: Calc Reporter: thegoldenear <petes-accounts>
Component: codeAssignee: oc
Status: CLOSED FIXED QA Contact: issues@sc <issues>
Severity: Trivial    
Priority: P3 CC: issues
Version: OOo 1.1 RC   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
Excel spreadsheet using OFFSET none

Description thegoldenear 2003-07-30 18:29:41 UTC
the attached spreadsheet was created and worked in MS Excel. OOo 1.1RC is unable
to calculate most of it, because it contains '=OFFSET(' rather than
'=SUM(OFFSET('. if you globally replace '=OFFSET(' with '=SUM(OFFSET(' it works ok

some examples of using OFFSET are:
http://www.meadinkent.co.uk/xloffsetchoose.htm
http://www.beyondtechnology.com/tips010.shtml
Comment 1 thegoldenear 2003-07-30 18:34:47 UTC
Created attachment 8171 [details]
Excel spreadsheet using OFFSET
Comment 2 oc 2003-07-31 10:15:06 UTC
Hi Eike,
to increase the interoperability to Excel we should change the behaviour 
Comment 3 niklas.nebel 2003-07-31 11:04:21 UTC
The problem is the "1" argument for height and width (4th and 5th
argument). We create an array reference whenever these arguments are
given, even if both are "1", and a single-cell reference only if they
are left out. This is consistent and documented, and I don't think we
should silently change it.
For compatibility with imported Excel documents, we have to find some
other solution.
Comment 4 ooo 2003-08-19 15:33:00 UTC
Just my 0.02 Euro:

First, for the sake of clarity: we don't create array references, but
cell-area references instead.

The main point is that area references used in a function expecting
single-cell references are position dependent. For example, in cell B1
the formula =A1:A2 results in the value of A1 being displayed, the
same formula =A1:A2 entered in cell B2 displays the value of A2. The
same formula entered in cell B3 results in a #VALUE error because the
positions don't correspond. This behavior is identical in the
competitive product.

The =OFFSET(...;1;1) formula in Calc results in something similar to
=A1:A1 and works only in one corresponding row respectively column,
resulting in #VALUE on other positions. I doubt that anybody uses this
special case behavior intentionally, and I plead for changing it to
the behavior the competitive product implements, and to document the
change.

But of course this is up to the User Experience team..
Comment 5 bettina.haberer 2004-01-29 20:06:32 UTC
Summary: spreadsheet / calc doesn't import Excel's OFFSET.
Hello Niklas, please give approval for this evaluated OO.o 2.0 flagged issue. 
If you confirm with the target OO.o 2.0, then please keep it on your owner (or
the owner of the concerning developer) for implementation. In case you want this
issue for 'OOo Later', then please reset the target milestone. If you decline
the issue finally, please set the resolution to 'Wontfix' (but do not close). In
case of 'OOo Later' or 'Wontfix' please reset it on Bettina's owner. Thank you.
Comment 6 niklas.nebel 2004-02-18 10:27:56 UTC
We have a list of possible formula enhancements for 2.0 in issue 20494. This one
isn't on the list, and probably less important, so we have to move it to "later".
Comment 7 ooo 2004-05-06 18:08:04 UTC
Grabbing issue.
Comment 8 ooo 2004-05-06 18:25:26 UTC
Fixed with issue 25924 on branch cws_src680_calcfixer:
sc/source/core/tool/interpr4.cxx  1.27.70.1
Comment 9 ooo 2004-05-13 10:54:48 UTC
Reopen to reassign.
Comment 10 ooo 2004-05-13 10:55:23 UTC
Reassign to QA.
Comment 11 ooo 2004-05-13 10:58:40 UTC
Restore status to resolved fixed.
Comment 12 oc 2004-06-02 13:46:12 UTC
verified in internal build cws_calcfixer
Comment 13 oc 2004-10-11 11:53:18 UTC
closed because fix available in master OOo1.9m54