Results 1 to 5 of 5

Thread: Vat formula not calculating correctly in Excel

  1. #1
    Email problem
    Join Date
    Aug 2014
    Location
    Durban
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Vat formula not calculating correctly in Excel

    Please could someone help with my formula for subtracting vat from a value in excel.

    The amount is R307 320.40 and if I divide by 1.14 on the calculator i'm getting R37741.10, but if i use the formula =D12/1.14 i'm getting R37 528.20. What am I doing wrong?

  2. #2
    Diamond Member Mike C's Avatar
    Join Date
    Apr 2012
    Location
    Umkomaas
    Posts
    2,778
    Thanks
    240
    Thanked 364 Times in 320 Posts
    Maybe you are using a decimal that appears to be rounded to 1.14 due to the format settings but is in fact something like 1.391
    Don't take the good times for granted!

  3. #3
    Email problem
    Join Date
    Aug 2014
    Location
    Durban
    Posts
    8
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Mike C View Post
    Maybe you are using a decimal that appears to be rounded to 1.14 due to the format settings but is in fact something like 1.391
    What do I need to do to change that Mike?

  4. #4
    Diamond Member Justloadit's Avatar
    Join Date
    Nov 2010
    Location
    Johannesburg
    Posts
    3,096
    Thanks
    118
    Thanked 639 Times in 543 Posts
    Blog Entries
    1
    You are making a calculation error some where in your cell, or the number in the cell is being influenced by some other factor.

    I am assuming that you are doing a VAT calculation, or calculating the VAT on a figure.

    There is 2 ways of doing it.
    307 320.40/ 1.14 = 269 579.30
    then 307 320.40 - 269 579.0 = 37 741.10

    or
    307 320.40 * 14 = 4 302 485.6
    then 4 302 485.6/114 = 37 741.10

    I did both in a new spreadsheet and came to the same answer.
    Victor - Knowledge is a blessing or a curse, your current circumstances make you decide!
    Solar pumping, Solar Geyser & Solar Security lighting solutions - www.microsolve.co.za

  5. #5
    Diamond Member Mike C's Avatar
    Join Date
    Apr 2012
    Location
    Umkomaas
    Posts
    2,778
    Thanks
    240
    Thanked 364 Times in 320 Posts
    Quote Originally Posted by NVRensburg View Post
    What do I need to do to change that Mike?
    Right-Click on the cells where you are doing your calculation. Goto Format cells ... Choose "Number" (under the Number Tab) and change decimal places to 4. If it is a decimal rounding issue, then change the figure to 1.1400 manually. You can then change your formatting back to 2 decimal figures.
    Don't take the good times for granted!

Similar Threads

  1. Calculating UIF on Bonuses
    By snowy170 in forum Accounting Forum
    Replies: 4
    Last Post: 13-Feb-16, 11:03 AM
  2. Calculating Depreciation
    By RCS01 in forum Accounting Forum
    Replies: 2
    Last Post: 30-Jan-14, 09:19 PM
  3. Calculating annual leave
    By KimH in forum Labour Relations and Legislation Forum
    Replies: 8
    Last Post: 07-May-13, 11:52 AM
  4. VAT apportionment formula
    By alanc in forum Tax Forum
    Replies: 2
    Last Post: 05-Dec-12, 03:37 PM
  5. the get rich quick formula
    By murdock in forum General Business Forum
    Replies: 3
    Last Post: 14-Apr-10, 07:32 AM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •