Help with a formula in Excel please....

[Gollom]

I have 2 cells, A1 and A2

I would like to initially enter a figure into A1 with the end result that the VAT element is displayed in A2 and the the net value is in A1

E.g. By intitially entering £10.00 in A1 the end result would be £8.51 in A1 and £1.49 in A2

Many thanks!

 

[david_h]

you cannot enter a number in A1 i.e. £10 and have it display the result £8.51 at the same time.

You need 3 cells

A1 to enter £10

A2 for VAT amount "=A1*0.175"

A3 for Net Amount "=A1-A2"

(type in quotes what I put in the cells.

 

[pammy]

Not sure you can do that. I think you'll need 3 cells - A1 would be your gross amount, A2 could then be your net amount and A3 the VAT amount. Fairly straight forward formula to do the calc though. But if it's essential that the gross amount is actually removed from view then I have no idea how you would do that other than using macros to switch between pages etc.

 

[pammy]

you cannot enter a number in A1 i.e. £10 and have it display the result £8.51 at the same time.

You need 3 cells

A1 to enter £10

A2 for VAT amount "=A1*0.175"

A3 for Net Amount "=A1-A2"

(type in quotes what I put in the cells.

Doesn't work as that calc's 17.5 % of £10 when £10 is actually 117.5% of the original amount. Therefore the formula needs to be:

A1/117.5*100

A3 is right though

you'll also need to set the cell attributes for the muber of decimal places you want to show.

 

[imadoofus]

Or you could usa a macro.

See attached

View attachment VAT Calculation.zip

PJ

 

[blassberg]

or you could format the gross cell (A1 in david_h's example) as white on white

 

[mobeyone]

Or you could hide the gross column? at leat you can always refer to the calc should you need to.

 

[blassberg]

agreed but if you do that you have to unhide every time you need to change it

 

[wallingd]

If a cell's hidden use F5 to goto then u can see what u'r editing in the formula bar.