westy159
In Runtime
- Messages
- 182
- Location
- United Kingdom
Hi folks,
Im creating a spreadsheet in Excel 2010 for claiming expenses (such as travel) at work. One requirement is that it should be possible to enter a price - either including or excluding VAT - and have the other amounts be automatically calculated.
Heres a screenie for the relevant part of the spreadsheet to help.
http://img820.imageshack.us/img820/5300/vatcalc.jpg
For simplicity sake we'll call the top left cell A1. So what should happen is that a user can enter a VAT excluded price, let's say £60, in to the left column and the amount including VAT (£72) will appear in the right column with the VAT (£12) appearing in the middle column. And they should be able to do this in reverse, so enter £72 in the right column and get £60 and £12 in the other columns. The totals of these 3 columns add up at the bottom.
Now I can place calculations for this in the respective columns (=C2/1.2 =C2-A2 =A2*1.2) and this works except when a user deletes an entry it will delete the formula with it, as well as that there's a problem with the two VAT exc / inc formulas going round in circles as they refer to each other.
Really cant think of a way around this. Thanks in advance!
Im creating a spreadsheet in Excel 2010 for claiming expenses (such as travel) at work. One requirement is that it should be possible to enter a price - either including or excluding VAT - and have the other amounts be automatically calculated.
Heres a screenie for the relevant part of the spreadsheet to help.
http://img820.imageshack.us/img820/5300/vatcalc.jpg
For simplicity sake we'll call the top left cell A1. So what should happen is that a user can enter a VAT excluded price, let's say £60, in to the left column and the amount including VAT (£72) will appear in the right column with the VAT (£12) appearing in the middle column. And they should be able to do this in reverse, so enter £72 in the right column and get £60 and £12 in the other columns. The totals of these 3 columns add up at the bottom.
Now I can place calculations for this in the respective columns (=C2/1.2 =C2-A2 =A2*1.2) and this works except when a user deletes an entry it will delete the formula with it, as well as that there's a problem with the two VAT exc / inc formulas going round in circles as they refer to each other.
Really cant think of a way around this. Thanks in advance!
Last edited:
