Mathematical order of operations problem? or something else?-CodePudding

Sample gsheet
https://docs.google.com/spreadsheets/d/1lRs27-1vcF-8SV8l2lwOHYtEVJewn7iurqm93C-KtcI/edit?usp=sharing

I think this two formula below supposed to give the same result, and it did when calculate separately. But when I composed the formula together with other cells, it gives 2 difference result. Somebody could explain the logic behind please? Thank You!

=SUM(ARRAYFORMULA($C$5:$C10*$D$5:$D10*$E$5:$E10))-SUM($F$5:$F10) =SUM(ARRAYFORMULA($C$5:$C10*$D$5:$D10*$E$5:$E10-$F$5:$F10))

CodePudding user response：

Logic is simple - for first formula:

=H2 I2 J2-SUM(ARRAYFORMULA($C$5:$C10*$D$5:$D10*$E$5:$E10))-SUM($F$5:$F10)

replacing ranges with values you get:

=79,478.14 - 41,000.00 - 64.16 => 38,413.98

for second formula:

=H2 I2 J2-SUM(ARRAYFORMULA($C$5:$C10*$D$5:$D10*$E$5:$E10-$F$5:$F10))

with values you get:

=79,478.14 - 40,935.84 => 38,542.30

You should use parentheses to get the same result:

=H2 I2 J2-(SUM(ARRAYFORMULA($C$5:$C10*$D$5:$D10*$E$5:$E10))-SUM($F$5:$F10))

with values :

=79,478.14 - (41,000.00 - 64.16) => 38,542.30