vat adds up correctly accross spreadsheet but down the columns whe

I have a very basic spread sheet which adds VAT to various cost figures.

I have a total VAT cell below the VAT columns. When adding the columns 
together the answer is normally out by 1p less.

Example:

Cost           VAT
£317.00      55.48
£317.00      55.48

Total VAT should be £110.96  but Excel calculates at £110.95  The above 
calculation may be a bad example but the above occurs when the VAT is rounded 
up or down against an individual cost figure. So why is the Total different 
if the sum is just the adding up of the VAT individual totals?

Thanks

0
10/20/2004 11:19:08 AM
excel.newusers 15348 articles. 2 followers. Follow

5 Replies
624 Views

Similar Articles

[PageSpeed] 59

because 17.5% of 317 is actually 55.475

Excel will display this as 55.48 but when you total the column it wil
actually use 55.47

--
Alex Delamai
-----------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127
View this thread: http://www.excelforum.com/showthread.php?threadid=27076

0
10/20/2004 11:26:13 AM
Excel is displaying 55.48 as the answer but is really thinking 55.475 is teh 
REAL answer.

55.475 and 55.475 = 110.95

You can use ROUND functions to predict and control how excel treats these 
numbers.

Hope this helps

"Boonerball" <Boonerball@discussions.microsoft.com> wrote in message 
news:5E8A7535-73EC-440B-9581-2EDB1A590EAC@microsoft.com...
>I have a very basic spread sheet which adds VAT to various cost figures.
>
> I have a total VAT cell below the VAT columns. When adding the columns
> together the answer is normally out by 1p less.
>
> Example:
>
> Cost           VAT
> �317.00      55.48
> �317.00      55.48
>
> Total VAT should be �110.96  but Excel calculates at �110.95  The above
> calculation may be a bad example but the above occurs when the VAT is 
> rounded
> up or down against an individual cost figure. So why is the Total 
> different
> if the sum is just the adding up of the VAT individual totals?
>
> Thanks
> 


0
10/20/2004 1:11:59 PM
Sorry did not complete my reply.

However you might want to look at the following site instead

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

--
Alex Delamai
-----------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1127
View this thread: http://www.excelforum.com/showthread.php?threadid=27076

0
10/20/2004 1:17:37 PM
In addition to Carl and Alex: consider the use of Tools>Options>Calculation, 
"Precision as displayed". Often a good idea for financial spreadsheets, but 
read HELP first to understand the implications.

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Carl Jarvis" <carljarvis@ohnothankyouforthespam.com> wrote in message 
news:1098277920.21898.0@spandrell.news.uk.clara.net...
> Excel is displaying 55.48 as the answer but is really thinking 55.475 is 
> teh REAL answer.
>
> 55.475 and 55.475 = 110.95
>
> You can use ROUND functions to predict and control how excel treats these 
> numbers.
>
> Hope this helps
>
> "Boonerball" <Boonerball@discussions.microsoft.com> wrote in message 
> news:5E8A7535-73EC-440B-9581-2EDB1A590EAC@microsoft.com...
>>I have a very basic spread sheet which adds VAT to various cost figures.
>>
>> I have a total VAT cell below the VAT columns. When adding the columns
>> together the answer is normally out by 1p less.
>>
>> Example:
>>
>> Cost           VAT
>> �317.00      55.48
>> �317.00      55.48
>>
>> Total VAT should be �110.96  but Excel calculates at �110.95  The above
>> calculation may be a bad example but the above occurs when the VAT is 
>> rounded
>> up or down against an individual cost figure. So why is the Total 
>> different
>> if the sum is just the adding up of the VAT individual totals?
>>
>> Thanks
>>
>
> 


0
nicolaus (2022)
10/20/2004 7:15:34 PM
Boonerball wrote:
> I have a very basic spread sheet which adds VAT to various cost
> figures.
>
> I have a total VAT cell below the VAT columns. When adding the columns
> together the answer is normally out by 1p less.
>
> Example:
>
> Cost           VAT
> �317.00      55.48
> �317.00      55.48
>
> Total VAT should be �110.96

No it's not. �634 x 17.5% is �110.95

Vat on �317 is �55.475. Twice �55.475 = �110.950 


0
GordonBP1 (262)
10/20/2004 7:45:46 PM
Reply:

Similar Artilces:

How to add attachments into the Knowledge Base?
Hi, How to add attachments into the Knowledge Base? I heard that its possible with some code. Is there anyone outthere with a small handy guide for that :-) or even a ready to use codesnippet? best regards Antonio I don't think it's possible, what our clients does is linked to an external resource like sharepoint for their attachements. If someone can figure out how to attach to a knowledge base, please share, I would like to know. thanks! Darren Liu Crowe Chizek and Company http://www.crowecrm.com On Jul 17, 12:30 am, A.K.V. <A...@discussions.microsoft.com> wrote: > ...

Microsoft office Live add in sign in error
I close word document and get Microsoft office Live add-in sign-in error. How do i stop this from happening. ...

VAT Receipt
Hi I am looking for a receipt that details Total Taxable amount by Tax code this is for customers claiming vat back on goods bought Thanks rsn ...

Excel Add-Ons For Charting
Is there an add-on for Excel that will draw contour plots? Hi, Have a look at fernando cinquegrani contour addin. http://www.prodomosua.it/ppage02.html Cheers Andy John Schutkeker wrote: > Is there an add-on for Excel that will draw contour plots? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

FWD: Check this corrective package for Microsoft Internet Explorer
--bioteguyvaqatnq Content-Type: multipart/related; boundary="sdecxbmqxqqjzyw"; type="multipart/alternative" --sdecxbmqxqqjzyw Content-Type: multipart/alternative; boundary="fiespgjposau" --fiespgjposau Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "November 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to protect your computer from these vulnerabilities...

VAT
I have forgotten what formula to insert in a cell for the calculation of VAT on the total of three previous columns any help please would be appreciated. Assuming that the 3 columns each represent the net amount, and each amount is subject to the standard rate of VAT in the UK =ROUNDDOWN(SUM($A1:$C1)*0.175,2) If however the facts are as above but the amounts in the column are VAT inclusive, then the VAT would be =ROUNDDOWN(SUM($A1:$C1)*(7/47),2) If there are zero-rated, reduced rate, exempt or outside-the-scope supplies, or if the supplies are subject to a rate of VAT applicable in ano...

Excel 2007, Add custom menu with macro
We work with office 2007, and I discover some options to add custom functions. In 2003 you easily could add a menu to the menu bar, in this menu I could add a label, which I assign a macro to execute. Quit easy done and very usefull. Now in Excel 2007 this is (as far as I can see) not quit easy done. I only can add command to the Quick Access Toolbar, but this are commands already present in Excel. Does someone have a hint how I can establish in 2007 what was easily done in 2003. In short: I want to hit a menu item which will execute a macro. Thanks in advance, Dirk Hi Doekoe If you want...

How do I add TextBox.values?
How do I add the Values of several Text Boxes And place it in Another Text Box. So the Sum of Text Boxes 1 to 13 is printed in text Box 14 Thanks for the Help. Wally As before, response in .programming. -- HTH RP (remove nothere from the email address if mailing direct) "WTG" <wtg_designs@hotmail.com> wrote in message news:9v8421dhqqn7dft2ipd8df4gufal04t12v@4ax.com... > How do I add the Values of several Text Boxes > > And place it in Another Text Box. > > So the Sum of Text Boxes 1 to 13 is printed in text Box 14 > > Thanks for the Help. > ...

Add to Global Address book
We are now on Exchange 2003 (SBS2003) and migrated from exchange 5.5, where you were able to add external email addresses to your global address book. We have serveral email addresses (not on our exchange server) that everyone in my company uses. Is their a way to add email addresses to the Global Address Book (without creating a user account &without going to each computer and adding it) Yes, refer to section "Creation of Mail Enables Contacts. http://www.msexchange.org/tutorials/User-Groups-Distribution-List-Contact-Management.html -- John Oliver, Jr. MCSE, MCT, CCNA, E...

How do I add totaled labels to a column chart?
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a graded column chart that features values for specific revenue streams on the y-axis and it is by year on the x-axis. I have all the revenue streams with labels for their values, but I want to add a label that will show the total amount of all the revenue streams added together for each year. How do I do this? I've been trying all combinations of data input and nothing is working for me. Thanks. ...

How do I add color to an image?
I have been sent an image that I have scanned into MicroSoft Publisher. The image is black and white. I would like to change it to a color image. Is this possible? If you want to edit the photo and it's colors, you will need an image editing program, such as Microsoft PhotoDraw 2000 v2, Microsoft Paint, Adobe Photoshop, Paintshop, etc. I recommend you scan the image into one of those programs instead of scanning into Publisher. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rig...

Creating a template but the fields I want to add is not there.
The template that I need to create will require data from Entity A and Entity B. I can only select one entity when I create a table. Is there a way I can create a template gathering data from Entity A and Entity B? Can someone please help me? Thank you. Hi Mike, You can select data from another entity if the original source entity has a parent relationship to that entity such that a child record has a single parent record to pull fields from. So a Template regarding the 'Contact' entity would be able to use fields from the 'Company' entity due to the 'parentcus...

How do I Add and Subtract percentages in cells
Can anybody help I am trying to add and subtract percentages from cells deducting tax from wages etc. How do i go about it.As i am new to excel Thanks in advance. Ron Ronnie Check out this on-line tutorial for Excel. http://www.usd.edu/trio/tut/excel/index.html Specifically the "Basic Math" section. http://www.usd.edu/trio/tut/excel/13.html Gord Dibben Excel MVP On Sun, 1 Aug 2004 23:06:56 +0100, "Ronnie" <ronnie@broraweb.co.uk> wrote: >Can anybody help I am trying to add and subtract percentages from cells >deducting tax from wages etc. >How do i ...

Creating an Add-On
I went here http://www.oaltd.co.uk/MVP/Default.htm to use the very nifty little utility called FindLink. I'm trying to figure out the way to make it an add-on. I think I'm missing a step. It's in my add-on list box, but it doesn't show up under tools when I restart. The way things are now I have to download it every time I want to use it. >>I'm trying to figure out the way to make it an add-on. FindLink is an add-in. Bill Manville created it as such. Whether is appears on Tools, Add-ins is a different issue. It doesn't have to go there as you can just F...

I want to add a button to a property sheet
I have a property sheet with two pages but I want to add buttons to the sheet but it has no dialog page associated with it. How can I do this? ...

Add Items Using Barcode
Is there a way to add items to a new database by merely scanning them and the fields would be populated? No - unfortunately, the system wouldn't know your specific costs, price, departments or categories, or the description you would want to assign to the item you scanned. But if you have an excel spreadsheet with items you want to import, if you have access to Customersource download the store operations import tool QSCIMP.EXE. Otherwise seek an RMS certified partner that can help you with data imports, setup, training and support - but make sure you screen two or three to make ...

Double Clicking on Xls/Xlsx Opens Excel But Does Not Show Spreadsheet
Excel 2007 If Excel is not open and I double click on an xls or xlsx file Excel starts but does not display the spreadsheet until I click on the Office button. Just clicking on the Office button displays the spreadsheet -- I do not have to select anything. If Excel is open and I double click on an xls or xlsx file the spreadsheet is immediately displayed in the running instance of Excel. Is this normal behavior or do I have some option set wrong? Try enabling one of the Excel Add-ins. Solver for instance. Stewart Berman wrote: > Excel 2007 > > If Excel is not o...

Add a button to a sheet to open a userform
I'm looking for a way to add a button to a spreadsheet that will open up a userform. I've seen it done before, I'm just not sure how do it. Thanks, Chris -- PokerZan ------------------------------------------------------------------------ PokerZan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23480 View this thread: http://www.excelforum.com/showthread.php?threadid=380247 Hello Chris Add a Command button to Excel double click it so it takes you to the vb code & enter UserForm1.Show (between Private Sub CommandButton1_Click() & End Su...

Add e-mail account to Outlook
I have been running a dial-up e-mail account with Outlook 2000 SP-3 on Windows XP home. I have recently added a separate cable modem connection using another ISP and want to maintain both e-mail services. I would like to add an account to Outlook for the new service, but haven't been able to figure out how to add any but a dial-up connection account. Is there a relevant Microsoft knowledge base paper on the subject? Here is a link to a knowledge base article on changing the connection type between LAN and Dial-up that might be useful, http://support.microsoft.com/default.aspx?s...

Combine multimple columns into one, automatically
I've posted the following question in the "excel.charting" newsgroup, but haven't gotten a response, so I'm wondering whether it isn't really a "charting" question, but is instead a problem that requires me to recalculate my data before drawing a chart. I'm trying to create a chart that will automatically combine into one column what are now several columns when displayed in an ordinary column chart. The columns I want to combine all have the same value on the X-axis, but are shown (in an ordinary chart) as separate columns because they have differe...

How to correct stalling in opening documents on Word 2003?
Recently I have been having a 10 second delay in opening and shutting down Microsoft Word 2003 documents. This does not happen on Exel 2003. Can anyone advise how I can resolve this stalling? ...

take name from the spreadsheet cell and get address from outlook
hi team Can some one advice wether using a as refernce to a cell which contain name of the person, can pull out the address from MS Outlook. Thanks Jithesh After you Export the Outlook Contacts to an xls file you will be successful. Jithesh wrote: > hi team > > Can some one advice wether using a as refernce to a cell which contain name > of the person, can pull out the address from MS Outlook. > > Thanks > Jithesh ...

How to add another line in cells with existing text?
When I hit the enter key it jumps to the next cell. I want to either move existing text to the next line, or add additional text to a new line in the same cell Press Alt+Enter Regards Trevor "jammer" <jammer@discussions.microsoft.com> wrote in message news:17D3DD8C-891B-41B7-A96C-353B3AD3B614@microsoft.com... > When I hit the enter key it jumps to the next cell. I want to either move > existing text to the next line, or add additional text to a new line in > the > same cell ...

Money 2005 Small Business
Hello, I am using Money 2005 Small Business. How do I add my company information on my customer invoices? I am able to add the company logo. Thx for your help. John On Sat, 27 Nov 2004 20:59:02 -0800, John Khan <John Khan@discussions.microsoft.com> wrote: >Hello, > >I am using Money 2005 Small Business. How do I add my company information on >my customer invoices? I am able to add the company logo. > >Thx for your help. > >John Hi John, I am able to go to the Tools menu, select Settings, and then select 'Business Information' from the list. It a...

How to get rid of Adobe add-in?
I have eliminated Adobe's additions to MS Office programs and they duly disappeared from all programs, except from Visio. I physically deleted the file C:\Program Files\Adobe\Acrobat 6.0\PDFMaker\Office\PDFMVisio.vsl, but now each time I start Visio, I get a warning: "Visio could not find this add-in or one of its files. This feature is not installed. Would you like to install it now?" I haven't been able to find where the add-in definition resides. Can anyone help me get rid of the nagging warning? ...