CODEWITHSUNDEEP
excelsum
noidea
noidea

Reputation: 184

Excel roundings not summing properly

I have a excel sheet with a few formulas like this:

A1,A2,A3= 0.13,1.25,2.21

A4: =(A1*A2)     =0.16 ( 2 decimal points)
A5: =(A2*A3)     =2.76 ( 2 decimal points)
A6: =SUM(A4;A5)  =2.93 ( 2 decimal points )

And i want to show 0.16+2.76=2.92

well, there's my problem in bold. i want to add the values from the cells, not the formuls result. How can i do that ? Thank you

Upvotes: 2

Views: 68

Answers (3)

AranDG
AranDG

Reputation: 406

You could wrap your formulas with the ROUND function:

=ROUND(A1*A2,2)

This will give you 0.16 as opposed to 0.163. Do this for each of your calculations and you'll only be calculating everything to two decimal places. Although I'm not sure why you'd want to do that.

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234665

Presumably you're working with money which is why you need this.

One way to resolve this is to use =ROUND(A1*A2, 2) etc. and base your subsequent calculations from that.

Do be aware though that you will still occasionally get spurious results due to Excel using a 64 bit IEEE754 floating point double to represent numbers. (Although it does have some extremely clever circumvention techniques - see how it evaluates 1/3 + 1/3 + 1/3 - it will not resolve every possible oddity). If you're building an accounting-style sheet you are best off working in pence, and dividing the final result.

Upvotes: 3

jcarroll
jcarroll

Reputation: 577

Round the values before you sum, ie:

=ROUND(A1*A2,2) 
=ROUND(A2*A3,2)

Upvotes: 2

Related Questions