Formula Addition incorrect

G

Gofljf

This must be a simple error, as all I am doing is calculating sales
tax.

I have column B as Gross rents, column c with the formula
=sum(B11_-Sum(b11/1.065). And column D with the net rent.

Then I ask it to total all columns down.

But the Column total from the calculations, does not add correctly. It
has a 3 cent difference. Add the figures manually and the total is
correct.

I.e.

Gross Rent Sales tax Net Rent
900 =Sum(b11)-sum(b11/1.065) 845.07


This column does not
add down correctly.

Can anyone suggest why. There are only 10 rows in this simple
calculation.

Cheers

Jack
 
B

Bernard Liengme

Firstly, to compute the Tax use =B11-B11/1.065
There is absolutely no need to involve the SUM function here!

The tax in your example computes to 54.92958....... (not 54.93)
So the net is 845.0704..... (not 845.07)

Your worksheet is formatted to show two decimal places but Excel stores the
value with 15 decimal places. So Excel sums using 15 decimals but you have
done a check using only 2. This is why you are off. This is called rounding
error.

Replace the Tax formula by =ROUND(B11-B11/1.065,2) to round the stored value
to two decimal places.

Visit this site to learn more http://www.mcgimpsey.com/excel/pennyoff.html
best wishes
 
P

Pete_UK

Use ROUND(your_formula,2) instead of your_formula. This will round the
calculation to cents, whereas at the moment you are displaying to cents
but may have 845.068 - such differences will mount up when you sum the
column.

Hope this helps.

Pete
 
N

Niek Otten

See

http://www.mcgimpsey.com/excel/pennyoff.html


BTW, Your formula can be written as =b11-(b11/1.065), no need for sum.


--
Kind regards,

Niek Otten

|
| This must be a simple error, as all I am doing is calculating sales
| tax.
|
| I have column B as Gross rents, column c with the formula
| =sum(B11_-Sum(b11/1.065). And column D with the net rent.
|
| Then I ask it to total all columns down.
|
| But the Column total from the calculations, does not add correctly. It
| has a 3 cent difference. Add the figures manually and the total is
| correct.
|
| I.e.
|
| Gross Rent Sales tax Net Rent
| 900 =Sum(b11)-sum(b11/1.065) 845.07
|
|
| This column does not
| add down correctly.
|
| Can anyone suggest why. There are only 10 rows in this simple
| calculation.
|
| Cheers
|
| Jack
|
|
| --
| Gofljf
| ------------------------------------------------------------------------
| Gofljf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34529
| View this thread: http://www.excelforum.com/showthread.php?threadid=542913
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top