Calculate VAT keeping the result positive and going to the last row

Hi Guys
There are numerous pages on the web helping with VAT calculations but I for an inexplicable reason cannot get my head round what I need to do. I will keep on working on it but as a last resort am turning to you guys. I have trawled through the posting. I am sure what I need is here but can’t find it. I had done calculations last year as are in “Transactions” but they are very clunky and use minus.

The sheet I am using is called “BDC2”
I want to calculate from the gross amount the vat.
The gross can be a minus number so I am using ABS to keep it all positive but in different columns.
I want to use what I am calling VAT_Multiplier table to chose the Vat. I have also shown the vat on the page so anyone looking can see it. I have 3 rates. Zero Standard = 20% and Reduced =5%.
The column length will vary as the rows are payments on an account per period.
This is not rocket science but I cannot get the answers correct.
I am given the gross amount and from the category the rate is entered. From these I need
Net – Vat amount Debit and Credit. In columns T - W.
Thanks in advance
Mark.

Your Value before vat is:

Please Login or Register  to view this content.

Your Vat is: Gross - Pre Vat value ie:

Please Login or Register  to view this content.

You seem to complicated the issue than it should be. If I were, I would not bothered with Look up and the rest. A single if statement could have done the job. You have rates in column R, from there, a single if statement could calculate the net, gross and any other variable.
I am lost on how you are working on Debit and Credit sides. As far as I know, these are the double entry book keeping:
INPUT (purchase/Expense) A/C

DR, Input Vat (purchase/Expense) and CR, Creditors/ Cash

OUTPUT (purchase/Expense) A/C
CR, Output(Sales/Income) and DR, Cash/Debtors.
All these can easily be driven from a single line.

Hi AB33
Thanks for this. I think that you are correct in that I have over complicated everything. Fact is that I totally lost the plot. I have not used Round as yet. Have to research what it does but the answers are great. I have put ABS in front of some to keep it al positive. I have also popped them into some code to copy to the last row I got from Norie earlier. Tested works fine.

Again thanks for this. I did feel embarrassed asking how to calculate VAT and you have no idea how pleased I am now

Mark