Description: A cell that has: =SUM(F6+$E$3)*(1+$E$2) produces the correct valuation When using the same calculation in an IF statement, it does not produce the same results: =IF(F6<$B$20,sum((F6+$E$3)*(1+$E$2)),F6*(1+$E$2)) (when the sum calculation is performed it apparently does not follow the correct order of operations. so both formulas implementations achieve different results. Steps to Reproduce: 1.F6 = 1000000 ; $E$3 = 18000 ; $E$2 = .08 2.=SUM(F6+$E$3)*(1+$E$2) produces 1099440 3.=IF(F6<$B$20,sum((F6+$E$3)*(1+$E$2)),F6*(1+$E$2)) produces: 1080000 Same results regardless of initial values so it's the operation that is faulty. Actual Results: 1.F6 = 1000000 ; $E$3 = 18000 ; $E$2 = .08 2.=SUM(F6+$E$3)*(1+$E$2) produces 1099440 3.=IF(F6<$B$20,sum((F6+$E$3)*(1+$E$2)),F6*(1+$E$2)) produces: 1080000 Same results regardless of initial values so it's the operation that is faulty. Expected Results: Should follow the same order of operations. Reproducible: Always User Profile Reset: no; happens on different systems. Additional Info: User-Agent: Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.98 Safari/537.36 OPR/44.0.2510.857
Please can you attach the sample file.
A few notes: The SUM() function takes a semicolon separated list of values (or cells or ranges). The reported examples give only one value as an expression, so the SUM function is not required here because SUM(x) == x. See simplification at end. The two expressions within the SUM call are not the same, although they appear to be equivalent (should produce the same result). Doing the math by hand either way, the correct result 1099440 is obtained. first: SUM( F6+$E$3 ) * (1+$E$2) ==> 1099440 [correct] second: SUM( (F6+$E$3)*(1+$E$2) ) ==> 1099440 [correct] (1000000 + 18000) * (1 + 0.08) = (1018000)*1.08 = 1099440 The erroneous result is 1080000 Note: 1080000 == (F6) * (1+$E$2) But this is the expression for the else clause in the =IF( ) formula that is actually producing the 'erroneous' result... I submit that $B$20 > F6 == 1000000 and the else result is being shown and SUM() is not even being called. Here is a simplification that may help: don't use the SUM function, just use the expression (F6+$E$3)*(1+$E$2) Unless there is a document that demonstrates this error in Calc, it is not a bug.
Created attachment 132956 [details] Test Apologies for my confusion, I got the comparison backwards at the end ... Correction: I submit there is nothing in $B$20 so F6 = 1000000 > 0 and the else clause is being evaluated, not the SUM. Otherwise the same: NOTABUG and don't need SUM() here Sample doc attached.
I was doing some more research as well and basically re-wrote the spreadsheet from scratch. I didn't go through a file to file comparison but it does appear that it is a user error on my part. Ticket can be closed, sorry for the hassle.