Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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$2:$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$2:$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.

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$2:$D6,"<0")
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$2:$D6,"<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")"