Help! - with a formula please

[chrissy]

I have 14 collums in use 1 of which is for VAT and the other for th
total
10 collums do have vat and 2 don't. I would like to know if there is
forumula I can use:
Col A ColB ColC ColD ColE Colf ColG GolH GolI Col J
vat Col total
vat inc nil v vatinc nil vatin vat nil vat vat
vat 17.5%

5.00 1.00 5.55
5.00 1.49 16.55

my question is what formula do I use in the total col? thank yo

 

[Bernie Deitrick]

Chrissy,

I think that you could use the SUMPRODUCT function to calculate the VAT, but
you would have to change your layout slightly.

Insert a new row 2, and in that row for the columns where the VAT applies,
put the 17.5%, and in the others, put 0.

Then you could usethis formula for prices in row 3:

=SUMPRODUCT($A$2:$J$2,A3:J3)

and then use

=SUM(A3:J3)

to get the subtotal, and

=SUMPRODUCT($A$2:$J$2,A3:J3) + SUM(A3:J3)

to get the overall total, with VAT included.

HTH,
Bernie
MS Excel MVP