1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

cross checking VAT fails?

Discussion in 'Business Applications' started by arrrgh2003, Apr 17, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    I have two sheets, one has cells for : amount =100, VAT =15, Gross =115
    I use VAT: F3=E3*15% and Gross: G3=E3*115% where E3 is the cost price.

    This all works fine but on another sheet I have the Gross from the customer and I want to enter the VAT of the Gross in another cell, to be added later for the nice tax man ;) H3 will be VAT of the gross G3
    For this i use: H3=G3-G3/(100%+15%)

    This gives the right answer for the VAT but my problems comes here,
    I only want it to enter the VAT figure if it checks the first sheet and the VAT is the same just as a cross check for wrong inputs.
    so, H3=IF(G3-G3/115%=Sheet1!F3,(G3-G3/115%),"ERROR")

    This was working fine for numbers like a gross of 1,150 because it gives a nice answer (1,000 and VAT 150) but I noticed it started to fail when I used 57,500, the result is ERROR even though the actual figure of 7,500 is the result which is correct?

    Is this because in the calculation, the figure is rounded in sheet2 (because of the divide by 115%) during the formula and even though the actual answer is correct it is seen as something else to excel? Can I force the cell to look at the result given and not the formula itself?

    Many thanks and sorry to babble on
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> Is this because in the calculation ...

    It's a calculation error. Excel has calculation errors.

    Take your =G3-G3/(100%+15%) ; if you copy it, then paste special (values), it'll still show on the sheet as 7500.00 (probably), but the formula bar will show 7499.999 ...

    To fix, you need to restrict your calculations using the ROUND function (2 DPs).

    Rgds,
    Andy

    EDIT: assuming you're talking UK VAT, shouldn't that be 17.5?
    http://www.taxcentral.co.uk/taxcentral/home/vat/vatrates/default.asp
     
  3. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    OK, so I don't do proper code. But I am trying to get my head round some aspects of it. You might want to have a go at some User Defined Functions, in a plain VBA module.

    Rgds,
    Andy

    Public Function VAT_Amount(Net)
    VAT_Amount = Round(Net * 0.175, 2)
    End Function

    Public Function VAT_Inc(Net)
    VAT_Inc = Round(Net * 1.175, 2)
    End Function

    Public Function Net_Of_VAT(Gross)
    Net_Of_VAT = Round(Gross / 1.175, 2)
    End Function

    Public Function VAT_from_Gross(Gross)
    VAT_from_Gross = Round(Gross - (Gross / 1.175), 2)
    End Function
     
  4. arrrgh2003

    arrrgh2003 Thread Starter

    Joined:
    Dec 4, 2003
    Messages:
    83
    thanks for that Andy, now i know how to see the 'real' result. Will try the round function later today.

    FYI the VAT is for for Zimbabwe so the 15% is right enough.

    VBA is beyond me just now, I'm just starting to look into it but cheers for the info.
     
  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    >> the 15% is right enough

    (y) (y) (y)

    They have VAT in Zimbabwe? If it's only 15%, maybe we should consider emigrating. :D

    >> VBA is beyond me just now, I'm just starting ...

    Well in case you change your mind, here's how in 5 easy steps.

    1. Tools -- Macro -- Record New.

    2 ; the "Record Macro" dialog. "Macro name" should default to "Macro1". With "Store in" set to "This Workbook", click OK.

    3. On the "Stop Recording" toolbar, click the "Stop Recording" button.

    4. Press ALT+F8, highlight "Macro1", click Edit. This takes you to the module.

    5. Delete the "Macro1" code, then paste in the VAT functions code. Amend any ".175" values to ".15", then close the Visual Basic Editor. Job done.

    You can then use the functions as you would Excel's regular functions (SHIFT+F3, User Defined category). As I posted them, they'd just read e.g.

    =VAT_Amount(B1)

    and the rounding is included so you wouldn't have to add it manually every time.

    Rgds,
    Andy
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/221332

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice