How do I correct this calculation?

R

Rebecca

My formula is SUM(B2:E2)-G2/G2 ... numbers are (7.58)-10.29/10.29.

Correct answer is - 0.2633 but Excel keeps inserting 6.57 as the answer.

Please help ... trying to calculate Total Cost on ALOT of spreadsheets.
 
T

Trevor Shuttleworth

=(SUM(B2:E2)-G2)/G2

=((7.58)-10.29)/10.29

(7.58)-10.29/10.29 G2/G2 = 10.29/10.29 = 1; 7.58 -1 = 6.58

The division takes precedence over the subtraction hence 10.29/10.29 ( = 1 )
so put the additional set of brackets in.

Regards

Trevor
 
H

Harlan Grove

Rebecca wrote...
My formula is SUM(B2:E2)-G2/G2 ... numbers are (7.58)-10.29/10.29.

Correct answer is - 0.2633 but Excel keeps inserting 6.57 as the answer.

Please help ... trying to calculate Total Cost on ALOT of spreadsheets.
Click to expand...

If you mean your SUM term returns 7.58, then 7.58-10.29/10.29 would
ALWAYS be evaluated as 7.58 - (10.29 / 10.29) = 7.58 - 1 = 6.58. That
you're getting 6.57 rather than 6.58 could be due to floating point
rounding error if your various terms aren't actually rounded to 2
decimal places but contain longer fractional parts.

If you want to subtract 10.29 from 7.58 then divide the difference by
10.29, you need to use parentheses to change the order of evaluation,
specifically, use

=(SUM(B2:E2)-G2)/G2

or

=SUM(B2:E2)/G2-1

In Excel and almost all other software that performs calculations of
pseudoalgebraic expressions, multiplication and division ALWAYS takes
place before addition or subtraction. This is EXACTLY THE SAME
convention that all math textbooks use and that you should have learned
in secondary school.
 
D

Difficult1

Try this: =(Sum(B2:E2)-10.29)/10.29

I tried it in a spreadsheet myself, and I came out to the answer you were
looking for.
 
Top