Exel Help

[silkman]

I've downloaded my bank statements in csv format.
I wanted to highlight my payments that are vat deductible and add them up at the end.
I also need to add up all payments made to me and add that up.
How do I do it?
Thanks in advance.

 

[MKFrost]

Guess there are a couple of ways to do this but the first and easiest that come to mind is to have the values in different columns.

So say you have your description in A and the amounts in B. Setup C to be your marker column. In C you will type in say '1' for a VAT item and a '2' for a payment to you.

In D you have your VAT items and in E the payments to you.

In the D column enter the formula =if(C2=1,B2-(B2/1.14),0)
In the E column enter the formula =if(C2=2,B2,0)

These formulas need to be copied down into each row in these columns. What will happen is if you enter a '1' in column C then the VAT will be calculated on the amount in B and this VAT amount will be entered into D.

If you enter a '2' in C the amount in B will be copied to E. You then just have to add up column D for your VAT and E for the payments to you.

 

[silkman]

Do I need to manually enter the formula each time in the cells with the corrseponding cell number?
If I highlight the entire column, it only applies the formula to D1

 

[MKFrost]

You need to copy the formula down. If you move your mouse over the cell in which the formula is there should be a little + sign if you move the mouse to the lower right corner. If you left click and then drag down it will copy the formula down. Or go onto the cell and hit CTRL C to copy, move down to the next cell and hit Shift and the down arrow to highlight the rest of the cells down, once done hit CTRL and V to paste the formula.

You then just need to go down your list and enter the 1's and 2's as required in column C.

 

[silkman]

Everything worked like a charm..Thanks so much