Why Calc gives different results for an Excel sheet?

English
, ,
#1

Hi,

there is an XLSX file which gives different results in Excel and Calc, but only in some rows. If you take a look on it the Excel results seem to be consistent, while Calc result are suspicious. This is why I ask it here and not on MSDN… :wink:
Unfortunately I cannot upload a file yet, I need 3 points to it. I cannot even upload an image about the phenomenon. I can manage an FTP or HTTP download (200 kB), of course, if someone might want to waste her/his time on my problem.

The formula in Hungarian Excel:
=HA($B11>=0;B11*C11;-(MAX(HA($E$2:$E11<-SZUMHA($B$2:$B11;"<0");$F$2:$F11))
-(SZUMHA($B$2:$B11;"<0")+MAX(HA($E$2:$E11<-SZUMHA($B$2:$B11;"<0");$E$2:$E11)))
*INDEX($C$2:$C11;HOL.VAN(MIN(HA($E$2:$E11>=-SZUMHA($B$2:$B11;"<0");$E$2:$E11));$E$2:$E11;0))
+SZUMHA(ELTOLÁS(D11;-1;0;-SOR(D11)+1;1);"<0")))

English equivalent
=IF($B11>=0;B11*C11;-(MAX(IF($E$2:$E11<-SUMIF($B$2:$B11;"<0");$F$2:$F11))
-(SUMIF($B$2:$B11;"<0")+MAX(IF($E$2:$E11<-SUMIF($B$2:$B11;"<0");$E$2:$E11)))
*INDEX($C$2:$C11;MATCH(MIN(IF($E$2:$E11>=-SUMIF($B$2:$B11;"<0");$E$2:$E11));$E$2:$E11;0))
+SUMIF(OFFSET(D11;-1;0;-ROW(D11)+1;1);"<0")))

Is there any known incompatibility in the formula? What is interesting: it appears in 14 cells, and the results are the same in all the 8 cells which give positive result, and in the first cell which gives negative result. All the other 5 cells with negative results are different.

Thanks for any help in advance: M. Gy.

#2

Go ahead and upload that sample. :wink:

#3

link text

#4

I have had a look at the Excel sheet. Could you explain the problem better?

#5

If you open this sheet in Excel, then you will see in cell D7 ($7 796,75), If you open it in Calc, you see ($8 201,27) in D7. There is also a difference in D9, D11, D12, D15. All the other cells in column D are the same both in Excel and Calc.

#6

The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-

SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")

In Excel this evaluates to 404.521 but in Calc this evaluates to 0.

A simpler formula that works correctly in both Excel and Calc is:-

SUMIF($D$1:$D6,"<0")

Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.

If you change the formula in D7 to the following, and then copy this cell to the other rows of column D then I think you should have the same results for both Excel and Calc.

=IF($B7>=0,B7*C7,-(MAX(IF($E$2:$E7<-SUMIF($B$2:$B7,"<0"),$F$2:$F7)) -(SUMIF($B$2:$B7,"<0")+MAX(IF($E$2:$E7<-SUMIF($B$2:$B7,"<0"),$E$2:$E7))) *INDEX($C$2:$C7,MATCH(MIN(IF($E$2:$E7>=-SUMIF($B$2:$B7,"<0"),$E$2:$E7)),$E$2:$E7,0)) +SUMIF($D$1:$D6,"<0")))

I also noticed you have entered the formula in column D as array formula, but that does not seem to be necessary. I think this is due to the functions MAX, MIN and SUMIF, which evaluate the result of a range of cells and do not return an array result.

You might also consider simplifying the formula by using helper columns to hold intermediate results of the calculation instead of using such a complicated formula in column D.

Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.

Edit to suggested functions to avoid a self reference in the formula when pasted to cell D2. Changed from “SUMIF($D$2:$D6,”<0")" to “SUMIF($D$1:$D6,”<0")"

#7

As -ROW(D7)+1 results in -6 The above quoted sub-formula +SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0") is violating a restriction (newHeight > 0) contained in the mandatory specification of the OFFSET function by OpenFormula. The zero value returned for the erroneous expression should thus be replaced by Err:502.
If needed in similar cases for parametrisation the mentioned sub-expression can be replaced by +SUMIF(OFFSET(D7;-ROW(D7)+1;0;ROW(D7)-1;1);">0").

#8

Might someone point to a specifiaction for OFFSET as used by Excel 2010, please? Is there any?

(Sorry: The replacing subformula in my comment above is containing ">0" instead of the correct "<0". I can no longer edit the older comment.)

#9

Excel OFFSET function. It looks like Excel OFFSET function is also defined as requiring a positive height but it seems this is not being enforced by Excel.

#10

@mark_t: Can you quote or loink to the definition/specification you are talking of?

#11

@Lupp, beginning of my last comment is the link, sorry if the words were hiding the link.

#12

Sorry! I must have missed the highlighting.
Thank you for the link. It goes, however, to a “support” page. Thus I cannot be sure again if the text there was produced by someone who thought he knew how OFFSET works, or by someone who knew how it was specified to work. If this is the same, and the guy mentioned first is always right with his assumption, there cannot be any bugs in the world. I found a few, however.

#13

By the way: Excel 2016 introduced two non-commutative accumulating functions (CONCAT and TEXTJOIN). Applying one of these woud require an additional specification about the order of the elements to process, in specific if the argument is a reference returned by OFFSET with a negative height. Why do I feel so sure that nobody considered this? MS has made a big mess of computing which I once appreciated for reasons no longer valid. .

#14

Thank you all for the help, I try to understand the solution and the comments and return back with the result… :slight_smile:

#15

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.