Ask Your Question
0

Calc - Offset function works may give diferrent result than Excel equivalent

asked 2017-04-10 20:24:50 +0100

Phillev gravatar image

It looks that the OFFSET(Reference; Rows; Columns; Height; Width) function of Calc will give a different result than its Excel equivalent when the argument 'Width' is negative

Offset (A3 ; 0 ; 0 ; 1 ; -3) gives as a result: - {A3} in Calc (v5.3.0.3) - {A1:A3} in Excel

Is there a way to make Calc return the same result as Excel?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-04-10 21:07:15 +0100

erAck gravatar image

updated 2017-04-10 22:22:08 +0100

Oh, a negative width.. one has to bow before the wisdom of Excel.. no, it's not implemented. And not specified, neither by ODF OpenFormula nor by OOXML, which in fact says "The width shall be positive". Also https://support.office.com/en-US/arti... says "Width must be a positive number". Are you sure? I'd consider that behaviour a bug. Btw, your example should give C1:C3 instead if at all.

However, I tried in Excel 2010 and that gives #REF! error, as expected.

edit flag offensive delete link more

Comments

If one day negative values for the fourth and the fifth parameter of OFFSET should be permitted, the function should be specified to return the references concerned by a negative value there in reverse order. This is against the grain, of course, but it might at least be useful in some special contexts. So many things are not.

Lupp gravatar imageLupp ( 2017-04-10 22:10:22 +0100 )edit

Sorry, my example is wrong: It should read Offset(C1,0,0,1,-3) which gives {C1} in Calc and {A1:C1} in Excel. The issue is that when I open a spreadsheet with Calc which was written in Excel (not by me), I get a different result as when opened with Excel.

Phillev gravatar imagePhillev ( 2017-04-10 23:34:48 +0100 )edit

With C1 that's reproducible. So Excel allows a negative width argument, contrary to what they documented and contrary to what they specified in OOXML, ECMA-376-1:2016 section 18.17.7.245 OFFSET ("The width shall be positive").

erAck gravatar imageerAck ( 2017-04-12 12:03:51 +0100 )edit
0

answered 2017-04-10 22:03:58 +0100

Lupp gravatar image

updated 2017-04-10 22:05:41 +0100

@Phillev: "Offset (A3 ; 0 ; 0 ; 1 ; -3) gives as a result: - {A3} in Calc (v5.3.0.3) - {A1:A3} in Excel".
This I simply disbelieve. The fifth parameter is the NewWidth in columns also in Excel. It should never produce a range of rows. The behaviour reported for Calc may be fact, but should be considered a bug (see below).
If you want to get access to A1:A3 by OFFSET based on A3 as the reference to start with, you have to use OFFSET(A3;-2;0;3;1) in Calc. If you want to get what OFFSET(StartRef; ro; co; rs; cs) is claimed to return in Excel even if rs and/or cs are negative generalized in Calc, you have to use
=OFFSET(StartRef; ro+IF(rs>=0;0;rs+1); co+IF(cs>=0;0;cs+1); ABS(rs);ABS(cs)).
Please note that NewHeight=0 or NewWidth=0 should be considered illegal for any implementation.
Play with this attached example.

In Calc OFFSET should work as specified here.
It is not so easy to find specifications concerning Excel. There should be a spcification for OFFSET, too, but I couldn't find one, and the help texts offered by support pages are notoriously unreliable. Thus I only can tell you: OpenFormula does not specify OFFSET in terms allowing for negative values in the fourth and/or the fifth parameter place. If you want to extend a reference "upwards" in rows you have to account for this on the second parameter place, and to work with an expression giving the new height as an absolute amount. That Calc OFFSET does not reject a negative height with an error message but seems to aplly ABS(NegativePseudoHeight)+1 should be considered a bug. For columns/width take the above respectively.

edit flag offensive delete link more

Comments

Thanks for your answer. The issue is that I open a spreadsheet which was created with Excel... and don't get the same result with Calc. Is there a workaround (such as changing Offset behaviour in Calc so that it patches Excel's)?

Phillev gravatar imagePhillev ( 2017-04-10 23:37:53 +0100 )edit

Sorry! I can't help with that issue. Excel is made to be a nightmare for those relying on compatibility. What way to sell the same thing again and again other than to re-decorate it a bit now and then and to occupy the developers of free competing software with a lot of nonsense instead of going to be better?
Your issue is the fault of the one who designed the sheets "using" an illogical variant which cannot be expected to work with reasonable software. I gave a workaround, however.

Lupp gravatar imageLupp ( 2017-04-10 23:51:24 +0100 )edit

@Philev: "Is there a ...{solution}... such as changing Offset behaviour in Calc ..."
Of course, this is impossible without reworking the source code of Calc.
But: I already gave you a workaround in the sense that the (somehow more complicated) formulas using it should work in Calc and in Excel as well. Shifting documents from LibO to MS and back is a bad idea anyway. And using negative values for height and width, too.

Lupp gravatar imageLupp ( 2017-04-11 00:02:50 +0100 )edit

Thanks anyway. It looks it is a bit complicated to use LibO and share documents with the rest of the world.

Phillev gravatar imagePhillev ( 2017-04-11 00:18:49 +0100 )edit

We have a software (Excel) specifying two parameters to only accept positive values (as @erAck told you), though not returning an error in case of misuse but some undocumented behaviour insteadt that may be changed one day without notice.
Someone commits the malpractice to rename that bug a feature and to "use" it.
Calc does not produce the same misbehaviour.
Let's blame Calc for it.
Did I understand your arguments?
Ok. Calc is not specified to do as Excel (which one?) does.

Lupp gravatar imageLupp ( 2017-04-11 00:45:52 +0100 )edit

LibreOffice is bound to do as the internationally approved odf standards specify.
MS announced to also support these standards.
Alas!

Lupp gravatar imageLupp ( 2017-04-11 01:03:50 +0100 )edit

@Lupp. I am not complaining about Calc. I just want to use Calc for my business, which involves working with the rest of the world. Like it or not, the "rest of the world" uses Excel. I raised this question because I found a different result when opening a 5 MO, 20 tab Excel spreadsheet sent by a customer. Every tiny difference in the behaviour of a Calc function is a hindrance to the use of LibO as an alternative to MSO.Unfortunately.

Phillev gravatar imagePhillev ( 2017-04-11 10:35:31 +0100 )edit

I understand your sitiation well. At least I think so. It's jut: If you need exact "Exel behaviour", you need Excel and nothing else. In addition you have to be prepared that different customers use different versions of Excel which may show different behaviour concerning some details.
It is the raison d'être of MS Office to push users as a crowd to the current version, or, currently, to their cloud applications. And they have very clever guys to find ways to do as they planned.

Lupp gravatar imageLupp ( 2017-04-11 11:34:20 +0100 )edit

I remember the time when my son assisted in a biz. They often got .doc files from customers they couldn't open with their own version of 'Word(TM)'. I was the volunteer then who opened these files, confidentiality aside, with StarOffice (5.2 at the time) and re-wrote them into clean doc files with this "alien" software.
Do you think MS were not able to teach their own software to do so? I don't. They did exactly as they wanted: Promotion by malfunction: Biz bought new version.

Lupp gravatar imageLupp ( 2017-04-11 11:41:51 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-10 20:24:50 +0100

Seen: 362 times

Last updated: Apr 10 '17