Excel question

Steve_xx

Excel help please.

I put a monetary figure in A1 and I want to show a percentage increase in B1. For example I put £10.00 in A1 and in B1, after the addition of VAT at 17.5% I want the VAT amount £1.75 to appear in B1, rather than the cumulative amount of 11.75. How do I do that please?

Also, I want to know how to put a monetary value in A1 and then subtract a percentage, and the percentage amount subtracted to appear in B1. For example I put £10.00 in A1 and I want to minus it by 10%. In B1 I want £1 to appear, rather than the £9 remainder. How do I do that?

*srjp*

Steve_xx wrote: »

Excel help please.

I put a monetary figure in A1 and I want to show a percentage increase in B1. For example I put £10.00 in A1 and in B1, after the addition of VAT at 17.5% I want the VAT amount £1.75 to appear in B1, rather than the cumulative amount of 11.75. How do I do that please?

Assuming the VAT amount is fixed at 17.5: enter this in B1: =A1*0.175

If it's not fixed then you'll need to type the rate (eg 17.5) into another cell which can be 'called', eg C1, in which case B1: =A1*(C1/100)

Steve_xx wrote: »

Also, I want to know how to put a monetary value in A1 and then subtract a percentage, and the percentage amount subtracted to appear in B1. For example I put £10.00 in A1 and I want to minus it by 10%. In B1 I want £1 to appear, rather than the £9 remainder. How do I do that?

This is the same as above.

Mobeer

For the first case, the simplest approach would be to enter a formula in B1 to mutliply A1 by 0.175, like "=A1*0.175".

A more sophisticated approach would be to put a text into another cell (e.g. E3) like "vat_rate". Enter 17.5 in cell E4. Make sure cell E4 is selected, then use menu option Insert>Name>Define to name this cell. Excel will suggest "vat_rate" by default, as it looks at nearby cells for a suitable name. Then in cell B1, enter formula "=A1*vat_rate/100". This approach makes it easy to update the spreadsheet if the prevailing rate of VAT changes, and also makes it clear what is being calculated in cell B1.

For your second example, enter into cell B1 the formula "=A1*10%". This gives the original value in A1 and the deduction in B1. You could put C1=A1-B1 to see the reminder value. For this case you could also use an approach like the one above if you want to be able to easily change the 10% deduction figure.

Steve_xx

Thanks folks - i've got it now.