Thursday, August 12, 2010

Why currency doesn't always add up in Excel

Well I say Excel this also applies to OpenOffice's Calc and, judging by the number of demands sent out by companies for £0.00, several accounts packages.

Imagine you've bought an item for £15 and VAT at 17.5% has to be applied to that. You run the sum and the total is £17.63. But you've bought two different items both at £15, not a problem add the next item on and total them to £35.25. Anyone spotting that?




If we run £15+£15 and then add VAT we do indeed see £35.25, but how can a price ending in a 3 double to a 5? Because of rounding. See £15+VAT is in fact £17.625 but the display is set to currency and that's only two decimal places so it automatically rounds it either up or down, but the programme still thinks of it as having three places; that invisible third number still counts. So double £17.625 and it correctly calculates it to £35.25.

But this isn't a problem if you subtotal and then add VAT? Possibly. Consider those companies that charge using fractions of a penny yes energy companies I'm looking at you.

So you use 50 units of 'energy' at £0.0513 per unit you owe £2.57 add the lower 5% rate of VAT and you owe £2.69. For anyone following along there, might be stating "Shouldn't that be £2.70?" well again possibly. Let's show our working.

50*0.0513 = 2.565. Now this will show as £2.57 however if I leave it at that I'm applying a 5% increase to 2.656 not £2.57 and that equals 2.69325 which will show as £2.69. Hopefully so far so clear except what happens when I pay my £2.69?

£2.69-£2.69=£0.00. So I'm in the clear? Think again 2.69325-2.69=.00325 which appears as £0.00. But 0.0325 is above 0.00 so you still owe the company money and out goes the letter demanding you pay the £0.00 you owe or they'll take legal action against you.

So how do you avoid this in, for example, Excel? You round the figures. Excel features three functions for this Round(), RoundUp(), and RoundDown(). enter the figure or formula and end it with a comma and the number of decimal places and the output will be exactly that. So:

Round(2.656*1.05,2)=2.69
RoundUp(2.656*1.05,2)=2.70
RoundDown(2.656*1.05,2)=2.69

The other method is to tell Excel to use the figures as displayed. So if you enter "=2.656*1.05" and display it as currency any subsequent use of this figure will use £2.69 and not 2.69325.

Why not use this all the time? Well at least on some versions of Excel it's a programme wide change not a spreadsheet level change which means it'll do it for all your spreadsheets even the ones you don't want it to. Secondly you might want to selectively pick the rounding; that is RoundUp() the charge for energy while RoundDown() the VAT that you'll end up having to pay to the government.


Not that I'm suggesting that's the way to use it of course.

2 comments:

walkerno5 said...

It gets much worse than that and is far deeper and scarier. When I found out about this, I nearly quit spreadsheets and became a monk.

I reproduce an incredulous email I sent out in March 2009;



I’ve just spent most of this morning trying to solve a problem that is virtually unsolvable because Excel cannot add up;





Try this, if you think you can handle the implications;



Type in a cell;



=(43.1-43.2)+1



The answer should be 0.9, right? Now take that little sucker up to about 15 decimal places. Frustrating isn’t it?



Of course, the Round function clears that up for you. Except it doesn’t once you then use that rounded number, and NOT if you’ve got a whole list of numbers with pre-rounded decimal places that form a journal to post into your new accounting software; they need to work in BINARY as well as base 10! Freakish and rubbish. I no longer love Excel.





http://news.cnet.com/8301-13554_3-9935074-33.html

FlipC said...

Yet if you take it to 30 decimal places the the nines become zeros. If you take the formula and then multiply it by 2 you get 1.8 to 30 decimal places.

If you take the formula and paste the value and double it you still get 1.8 to 30 decimal places. So this seems as if if could only be a problem at a high decimal count and doesn't affect subsequent calculations.

You also don't need that complicated an equation remove the +1, strip the brackets and take the answer which should be -0.1 to multiple places and you'll see

-0.100000000000001000000000000000

Also incidentally although they state OO Calc is fine with the example they use. If you use yours it also returns the same figure. So you can't love Calc either :-P