cross checking VAT fails?

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

arrrgh2003

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

XL Guru

>> 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

XL Guru

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

arrrgh2003

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.

XL Guru

>> the 15% is right enough

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

>> 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

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On