1p out again!

[mlv]

I have an Excel sheet that calculates Flat Rate VAT (at 9%) on monthly gross
sales, and then deducts that VAT amount and any company expenditure for that
month from the gross sales amount to return the balance.

All the cells are formatted as (UKP) currency to 2 decimal places.

For example:

Cell A1 shows a gross sales value of £5,522.50

Cell A2 shows a VAT value of £497.03 (calculated as 9% of the value in Cell
A1)

Cell A3 shows an expenditure value of £350.20

Cell A4 shows the balance =A1-A2-A3

The result in Cell A4 should be £4,675.27, but the result I get is
£4,675.28. I conclude this is because the true VAT value in Cell A2 is
£497.025, which Excel displays as rounded up to £497.03 for 2 decimal
places.

Unfortunately, when Excel does the calculation for Cell A4, it uses the Cell
A2 true VAT value of £497.025. This gives the result of £4,675.275, which
is then rounded up to £4,675.28, giving the 1p error.

What is the best way to prevent this from happening? Can I force the
formula in Cell A4 to use the displayed figure in Cell A2, or is there a
more elegant way of resolving this problem?

 

[Keith74]

try putting this or similar in your a2 calc

=Round((A1*0.09),2)

hth

Keith

 

[Bob Phillips]

or you could also use

=A1-ROUND(A2,2)-A3

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[aidan.heritage]

I have an Excel sheet that calculates Flat Rate VAT (at 9%) on monthly gross
sales, and then deducts that VAT amount and any company expenditure for that
month from the gross sales amount to return the balance.

All the cells are formatted as (UKP) currency to 2 decimal places.

For example:

Cell A1 shows a gross sales value of £5,522.50

Cell A2 shows a VAT value of £497.03 (calculated as 9% of the value inCell
A1)

Cell A3 shows an expenditure value of £350.20

Cell A4 shows the balance =A1-A2-A3

The result in Cell A4 should be £4,675.27, but the result I get is
£4,675.28. I conclude this is because the true VAT value in Cell A2 is
£497.025, which Excel displays as rounded up to £497.03 for 2 decimal
places.

Unfortunately, when Excel does the calculation for Cell A4, it uses the Cell
A2 true VAT value of £497.025. This gives the result of £4,675.275, which
is then rounded up to £4,675.28, giving the 1p error.

What is the best way to prevent this from happening? Can I force the
formula in Cell A4 to use the displayed figure in Cell A2, or is there a
more elegant way of resolving this problem?

It's a common misconception that changing the DISPLAYED value changes
the STORED value, but as you've found out it DOESN'T - so I would
always suggest using a ROUND function (as has been suggested in other
replies) in any calculation where the result is going to be used for
another calculation and SHOULD be used in it's rounded format.

 

[Bernard Liengme]

You should read this:
http://www.mcgimpsey.com/excel/pennyoff.html
best wishes

 

[mlv]

Problem solved!

Thanks for your help, guys.

The article at:

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

was very useful.