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:
Is there a way to make Calc return the same result as Excel?
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/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66 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.
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.
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.
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”).
@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.
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)?
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.
@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.
Thanks anyway.
It looks it is a bit complicated to use LibO and share documents with the rest of the world.
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.
LibreOffice is bound to do as the internationally approved odf standards specify.
MS announced to also support these standards.
Alas!
@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.
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.
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™’. 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.