Board logo

Excel formula help please
Thinking about it - 14/3/12 at 07:38 PM

Right I have a spreadsheet in cell A1 I have an amount ie £50.
In cell B1 I can select the relevent % VAT from a drop down menu i.e 0, 5 or 20.
What is the formula I need to enter into cell C1 to display the VAT element of the £50 in cell A1?

Come on folks I know you won't let me down.

cerbera - 14/3/12 at 07:53 PM

In the cell C1 type '=A1*B1/100' without the quotes .... I think

PSpirine - 14/3/12 at 08:03 PM

It depends

If the £50 is the Ex.VAT price, then in C1 enter =A1*B1/100

This will give you the value of VAT (e.g. at 20% it would return £10)


If the £50 is the Inc. VAT price, then in C1 you have to enter = A1-(A1/(1+B1/100))

This will return the value of VAT included in the £50 (at 20% it would be £8.33)

Thinking about it - 14/3/12 at 10:24 PM

quote:
Originally posted by PSpirine
It depends

If the £50 is the Ex.VAT price, then in C1 enter =A1*B1/100

This will give you the value of VAT (e.g. at 20% it would return £10)


If the £50 is the Inc. VAT price, then in C1 you have to enter = A1-(A1/(1+B1/100))

This will return the value of VAT included in the £50 (at 20% it would be £8.33)




It was the latter wanted, Cheers