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,850
    Thanks
    249
    Thanked 371 Times in 327 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
    No act of kindness, no matter how small, is ever wasted. - Aesop "The Lion and the Mouse"

  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,480
    Thanks
    134
    Thanked 695 Times in 593 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,850
    Thanks
    249
    Thanked 371 Times in 327 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.
    No act of kindness, no matter how small, is ever wasted. - Aesop "The Lion and the Mouse"

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
  •