# Thread: Vat formula not calculating correctly in Excel

1. ## 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. 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

3. Originally Posted by Mike C
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. 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.

5. Originally Posted by NVRensburg
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.