Reverse Formulas from the Total

[Amanda Faramus]

	Column A	Column B	Column C	Column D
	Client	Net Amount	VAT at 20%	Gross Total
Row 1	Fred Blogs			£1,500
Row 2	***			£2,000
Row 3	** ***			£3,000

<tbody>
</tbody>

Hello,

I am not sure if this is possible.

I would like to be able to add formulas to column B and column C, Rows 1, 2 and 3, so that when I add the gross total (as you can see the £1,500 in Row1/Column D) in my table above the other two automatically show the Net Amount and VAT at 20% amount. Is this in fact possible?

Row 1/column B should be £1,250
Row 1/column C should be £250

I am sorry if I have not explained this very well.

Thanks

Amanda

 

[Comfy]

There are a couple of ways.

Excel 2010
	A	B	C	D	E
1		Column A	Column B	Column C	Column D
2		Client	Net Amount	VAT at 20%	Gross Total
3	Row 1	Fred Blogs	£1,250.00	£250	£1,500
4	Row 2	***	£1,666.67	£333	£2,000
5	Row 3	** ***	£2,500.00	£500	£3,000
Sheet1

Cell Formulas
Range	Formula
C3	=E3/1.2
D3	=E3/6

 

[Amanda Faramus]

There are a couple of ways.

Excel 2010

	A	B	C	D	E
1		Column A	Column B	Column C	Column D
2		Client	Net Amount	VAT at 20%	Gross Total
3	Row 1	Fred Blogs	£1,250.00	£250	£1,500
4	Row 2	***	£1,666.67	£333	£2,000
5	Row 3	** ***	£2,500.00	£500	£3,000

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C3 =E3/1.2 D3 =E3/6 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you so much for the response it works perfectly.

 

[Amanda Faramus]

Thank you so much Comfy it worked perfectly.