Results 1 to 5 of 5

Thread: Vat formula not calculating correctly in Excel

  1. #1
    New Member
    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,705
    Thanks
    231
    Thanked 355 Times in 311 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
    People will forget what you said, people will forget what you did, but people will never forget how you made them feel. - Maya Angelou

  3. #3
    New Member
    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
    2,901
    Thanks
    107
    Thanked 592 Times in 502 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 and LED lighting solutions - www.microsolve.co.za

  5. #5
    Diamond Member Mike C's Avatar
    Join Date
    Apr 2012
    Location
    Umkomaas
    Posts
    2,705
    Thanks
    231
    Thanked 355 Times in 311 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.
    People will forget what you said, people will forget what you did, but people will never forget how you made them feel. - Maya Angelou

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
  •