Formula Addition incorrect

[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

[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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gofljf" <[email protected]> wrote in
message news:[email protected]...
>
> 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...o&userid=34529
> View this thread: http://www.excelforum.com/showthread...hreadid=542913
>

[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

[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

"Gofljf" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=34529
| View this thread: http://www.excelforum.com/showthread...hreadid=542913
|

[Gofljf]

Thanks everyone for the help with my problem.

Cheers

Jack