Excell help: VAT calculation formula working both ways.

[bigboy529]

Hi all
I'm looking for a way to calculate VAT going both ways, if I only have the excluding price I want it to calculate the including, but if I only have the including price I want it to calculate the excluding in the same cells.

Example
Colom A is excluding VAT and colom B is including VAT.
If I enter R100 in to cell A1, I want cell B1 to display R114
But if I only have the inclusive price, I want to enter the R114 in to cell B1, then cell A1 must display R100.

Both formulas are easy enough to do on their own across different coloms, but how do I do this in the same range of cells and coloms as in my example if I only want to use colom A and B?

 

[DWAAS]

Can't do it -- you can't type a value in any cell containing a formula, this will clear the formula.
Unless you do it as a once-off

 

[bigboy529]

Can't do it -- you can't type a value in any cell containing a formula, this will clear the formula.
Unless you do it as a once-off

There must be some way, I've been trying Google, but guess I'm not searching for the correct thing.

 

[Connection]

You can do it but not with a formula. What you'd have to do is create an event trigger macro. So when something changes on the sheet, the macro checks if it took place in a specified range (eg A1 or B1) and based on where the change happened, the macro would update the value in the corresponding cell.

Easy peasy! Google event trigger macro for Excel.

 

[DWAAS]

You can do it but not with a formula. What you'd have to do is create an event trigger macro. So when something changes on the sheet, the macro checks if it took place in a specified range (eg A1 or B1) and based on where the change happened, the macro would update the value in the corresponding cell.

Easy peasy! Google event trigger macro for Excel.

Ah now I've learned something.

 

[Hamish McPanji]

Column C and D both contain formulae. C contains without VAT , D contains with VAT .
Formulae use if statements to decide whether a calculation us needed or not

You enter values in columns A or B, and C and D calculate the with/without for you. It's even better if you enter values on a different sheet, and the calculated values are on another sheet. Looks cleaner