Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 07 Sep 2016 09:58:18 +0200why Calc gives different results for an Excel sheet?https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/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.Mon, 05 Sep 2016 12:06:39 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/Comment by MGy for <p>Hi,</p>
<p>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.</p>
<p>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")))</p>
<p>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")))</p>
<p>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.</p>
<p>Thanks for any help in advance: M. Gy.</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76599#post-id-76599[link text](http://www.mgyhardsoft.hu/calcexcel/FIFOfunction.xlsx)Mon, 05 Sep 2016 14:42:26 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76599#post-id-76599Comment by Zeca for <p>Hi,</p>
<p>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.</p>
<p>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")))</p>
<p>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")))</p>
<p>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.</p>
<p>Thanks for any help in advance: M. Gy.</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76602#post-id-76602I have had a look at the Excel sheet. Could you explain the problem better?Mon, 05 Sep 2016 15:26:41 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76602#post-id-76602Comment by MGy for <p>Hi,</p>
<p>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.</p>
<p>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")))</p>
<p>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")))</p>
<p>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.</p>
<p>Thanks for any help in advance: M. Gy.</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76611#post-id-76611If 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.Mon, 05 Sep 2016 17:00:07 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76611#post-id-76611Comment by floris v for <p>Hi,</p>
<p>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.</p>
<p>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")))</p>
<p>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")))</p>
<p>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.</p>
<p>Thanks for any help in advance: M. Gy.</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76587#post-id-76587Go ahead and upload that sample. ;)Mon, 05 Sep 2016 12:18:15 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76587#post-id-76587Answer by mark_t for <p>Hi,</p>
<p>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.</p>
<p>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")))</p>
<p>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")))</p>
<p>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.</p>
<p>Thanks for any help in advance: M. Gy.</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?answer=76643#post-id-76643The 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")"Mon, 05 Sep 2016 23:50:12 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?answer=76643#post-id-76643Comment by Lupp for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76744#post-id-76744By 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. .Tue, 06 Sep 2016 22:53:09 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76744#post-id-76744Comment by Lupp for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76685#post-id-76685Might 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.)Tue, 06 Sep 2016 13:01:22 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76685#post-id-76685Comment by mark_t for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76736#post-id-76736@Lupp, beginning of my last comment is the link, sorry if the words were hiding the link.
https://support.office.com/en-us/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66Tue, 06 Sep 2016 22:24:30 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76736#post-id-76736Comment by MGy for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76769#post-id-76769Thank you all for the help, I try to understand the solution and the comments and return back with the result... :-)Wed, 07 Sep 2016 09:58:18 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76769#post-id-76769Comment by mark_t for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76694#post-id-76694[Excel OFFSET function](https://support.office.com/en-us/article/OFFSET-function-C8DE19AE-DD79-4B9B-A14E-B4D906D11B66). It looks like Excel OFFSET function is also defined as requiring a positive height but it seems this is not being enforced by Excel.Tue, 06 Sep 2016 14:59:15 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76694#post-id-76694Comment by Lupp for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76729#post-id-76729@mark_t: Can you quote or loink to the definition/specification you are talking of?Tue, 06 Sep 2016 21:07:33 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76729#post-id-76729Comment by Lupp for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76683#post-id-76683As `-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")`.Tue, 06 Sep 2016 12:46:36 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76683#post-id-76683Comment by Lupp for <p>The difference between Excel and Calc (using formula in D7) seems to be in the evaluation of :-</p>
<pre><code>SUMIF(OFFSET(D7,-1,0,-ROW(D7)+1,1),"<0")
</code></pre>
<p>In Excel this evaluates to 404.521 but in Calc this evaluates to 0.</p>
<p>A simpler formula that works correctly in both Excel and Calc is:-</p>
<pre><code>SUMIF($D$1:$D6,"<0")
</code></pre>
<p>Again this is for the formula in D7. Note the location of $ and in particular that this is not prior to the 6.</p>
<p>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.</p>
<pre><code>=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")))
</code></pre>
<p>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.</p>
<p>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.</p>
<p>Note. I used LO 5.2.0.4 and Excel 2010, on Windows 8.1.</p>
<p>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")"</p>
https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76742#post-id-76742Sorry! 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.Tue, 06 Sep 2016 22:43:53 +0200https://ask.libreoffice.org/en/question/76586/why-calc-gives-different-results-for-an-excel-sheet/?comment=76742#post-id-76742