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…
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.