Rounding on Excel spreadsheets - The Book-keepers Forum (UK)

ProBowlUK · Senior Member

Rounding on Excel spreadsheets

Shamus · Forum Moderator & Expert

Use the function ROUNDDOWN rather than ROUND in your formulae.

.......A
1 26.9
2 (8.8)
3 15.5492

For example, in column B1 put the formula =rounddown(a1,0) would result in 26, not 27 being returned
Similarly =rounddown(a3,2) would result in 15.54 rather than the 15.55 that would normally be returned when taking precision down to two decimal places.

Shaun.

P.S. had to edit it as it scrunched all of the spaces together. In the real world ignore the dots. Basically A is the column, 1,2,3 are the rows.

-- Edited by Shamus on Tuesday 19th of January 2010 02:00:26 PM

ProBowlUK · Senior Member

Shamus wrote:

Use the function ROUNDDOWN rather than ROUND in your formulae.

Similarly =rounddown(a3,2) would result in 15.54 rather than the 15.55 that would normally be returned when taking precision down to two decimal places.

Great, thanks mate.

This is what I've used for the VAT column ... =ROUNDDOWN((F15*0.175),2)

ProBowlUK · Senior Member

Hi

My Problem has been that my VAT answers rarely matched the textbook answers.

Now I have another problem, 2 results don't match ...
a) calculating VAT on individual items and totalling.
b) calculating VAT on invoice total.

Net	VAT	Gross

0.21	0.04	0.25
0.21	0.04	0.25
0.21	0.04	0.25
0.21	0.04	0.25
0.21	0.04	0.25
1.05	0.20	1.25

1.05	0.18	1.23

Which is the correct method ?

What formular is used so that both results are the same ? confuse

cheers

Shamus · Forum Moderator & Expert

Morning Bob,

I assume that the 0.04 came from the book?

The actual answer to .21 *.175 is .3675 * 5 is .18375 rounded down is .18.

Both answers are correct. On an individual basis .04 is the closest. On an accumulated basis it's .18.

Calculated in reverse from .25 the answer is different. .25 *7/47 = .037234 resulting in a net figure of .212766 which would have resulted in a total of 1.06 rather than 1.05.

In this instance where every value is the same you have a problem in that rounding up and rounding down don't average the figures as would be the case in most real life situations.

If you want the figures to add correctly the answer for your case is to use the calculations in the VAT field rather than typing .04 in the field. When totaled the 5 * .04 will total .18, not .20.

Same with the Gross column. Add calculations rather than typed in values from the book.

You could show the precision to more than two decimal places but we don't tend to do that in normal bookkeeping and rounding errors are something that is born providing that such is reasonable.

If you think about the VAT return they don't even want to know about the pennies, only the pounds.

Hope that this helps mate,

cheers,

Shaun.

ProBowlUK · Senior Member

Thanks for that, but I'm still not clear about the right way to set up VAT calculations on a spreadsheet to do some BK excercises.

Maybe I'm being confused by ... on an invoice it is possible to have items that should go to different Accounts in the books.
eg: supply of a new computer that also has paper and spare ink, staples. etc.

Shamus · Forum Moderator & Expert

Hi Bob,

see what you mean in that for some items on an invoice may be VATable whilst others may not.

Good example might be an invoice for Software at 17.5% VAT and books to train you in use of the software at 0% VAT.

The HMRC website states that :

For each different type of item listed on the invoice, you must show:

* the unit price or rate, excluding VAT
* the quantity of goods or the extent of the services
* the rate of VAT that applies to what's being sold
* the total amount payable, excluding VAT
* the rate of any cash discount
* the total amount of VAT charged

Therefore, it would seem for a mixed invoice you need to put the VAT rate (17.5%, 15%, 5% etc.) against each line item rather than on the line directly below net as is normal practice.

The net total would be the total of all line items as normal but the VAT figure would be the sum of all VAT items at each applicable rate. So, in the freaky situation where you had VAT at 17.5% and 5% and Exempt you would have seperate VAT totals for 17.5% VAT and 5% VAT.

There of course wouldn't be any confusion from VAT line items not totaling up to the VAT total.

Example (1) Standard Case
Description	. Price	. Qty	. Amount
Office 2007	75.00	5	375.00

		Net	375.00
		VAT @ 17.5%	65.63
		Invoice Total	440.63



Example (2) Simple Mixed Case
Description	VAT Rate	. Price	. Qty	. Amount
Office 2007	17.50%	75.00	5	375.00
Teach Yourself Office 2007	0.00%	15.00	10	150.00

			Net	525.00
			VAT	65.63
			Invoice Total	590.63


Example (3) Multi rate mixed case
Description	VAT Rate	. Price	. Qty	. Amount
Planning software	17.50%	75.00	5	375.00
Heating System Valve	5.00%	12.00	2	24.00
Heating System Widgets	5.00%	4.00	20	80.00
Teach yourself plumbing	0.00%	15.00	10	150.00

			Net	629.00
			VAT @ 17.5%	65.63
			VAT @ 5%	5.20
			Invoice Total	699.83

Hope that the above makes sense,

cheers,

Shaun.

P.S. had to edit it a couple of times as lost some of it's formatting. Contents are still the same though.

-- Edited by Shamus on Monday 1st of February 2010 01:41:50 PM

-- Edited by Shamus on Monday 1st of February 2010 01:44:40 PM

-- Edited by Shamus on Monday 1st of February 2010 01:48:23 PM

-- Edited by Shamus on Monday 1st of February 2010 02:07:04 PM