VAT Contents of Receipts

[AllisterB]

The attached sheet shows various items that are sold - some are subject to 10% VAT others 0% VAT.

The sales value is received in the third table.

I need a formula that will tell me the VAT content of the receipts for each of the months. i.e. how much VAT is in the $350 received in March.

I would prefer not use an array formula due to the large number of formulae that will be needed.

I have been trying to use SUMPRODUCT but haven't been able to crack it.

Thank You.

Allister

VAT.xlsx
	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
11				VAT exclusive								VAT Inclusive								Received
12		VAT		Jan	Feb	Mar	Apr	May				Jan	Feb	Mar	Apr	May				Jan	Feb	Mar	Apr	May
13	A	10%		100	200	300		400		1000		110	220	330	0	440		1100		0	50	100	0	0		150
14	B	0%			300					300		0	300	0	0	0		300		0	0	0	0	0		0
15	C	10%					200			200		0	0	0	220	0		220		0	0	220	0	0		220
16	D	10%			100					100		0	110	0	0	0		110		0	0	30	0	0		30
17	E	0%				200				200		0	0	200	0	0		200		0	80	0	20	0		100
18
19																				0	130	350	20	0		500
Sheet1

Cell Formulas
Range	Formula
R13:V17	R13	=ROUND(J13*(1+$H13),0)
P13:P17,AF13:AF17,X13:X17	P13	=SUM(J13:N13)
Z19:AD19,AF19	Z19	=SUM(Z13:Z18)

 

[jasonb75]

@ebea

AD15 does appear to be an anomaly if intermediate totals are necessary then Subtotal would be a wiser choice.

I don't see that using Sum instead of Sumproduct will make a difference, it would be marginally less efficient and would still encounter the error of a mathematical operation being attempted on a non numeric value.

 

[ebea]

@ebea

AD15 does appear to be an anomaly if intermediate totals are necessary then Subtotal would be a wiser choice.

I don't see that using Sum instead of Sumproduct will make a difference, it would be marginally less efficient and would still encounter the error of a mathematical operation being attempted on a non numeric value.

If you setup the Fomula a little bit different, he can use Sum. SUM are not affected of Text in cells, but Sumproduct are. If I understand the question correct, he want to find the amount of VAT received in table 3. And if he calculate by using SUM in AD Column, and subtract the Sum he have in Table 2, and set up the right way, he should get the result (if I understand the question correctly )

 

[jasonb75]

If you setup the Fomula a little bit different, he can use Sum.

Please, feel free to show us how. If you're seeing something that I'm not, I would like to know what it is.

SUM are not affected of Text in cells, but Sumproduct are.

Both are affected when you perform any operations inside the function, likewise neither are affected if there are no operations performed inside the function.

I can't see any way of doing it differently with Sum, other than setting up an entire table in the same format and using a formula for every row instead of just one at the end of the column, then repeating this for every month. Potentially turning a line of 12 sumproduct formulas into a table of hundreds of sum formulas.

 

[ebea]

You write: " I need a formula that will tell me the VAT content of the receipts for each of the months. i.e. how much VAT is in the $350 received in March" And if I have understood this correctly, why not use, a SUM formula, as this here: =SUM(AD13:AD19)*$B$2 insert in AD22. The $B$2 in the Formula, are just the percent value insert in B2 (10%). It should give you the amount of the VAT, of the received amount.

 

[jasonb75]

You write: " I need a formula that will tell me the VAT content of the receipts for each of the months. i.e. how much VAT is in the $350 received in March"

I actually provided the answer, not the question. Regardless of that, it looks like you may have misunderstood what was asked. Hopefully this will help you to understand a little better.

If you look at the data sample provided by AllisterB you will see that the percentage is variable by row, which is why sumproduct is needed. Your suggestion assumes that the VAT percentage is the same on every row. In order to produce the correct results the calculation needs to be performed on a row by row basis (using an array) which can not be done the way that you suggest.

Also, =SUM(AD13:AD19)*$B$2 is calculating the result as a percentage of the total amount, where the correct result needs to be calculated on the pre-tax amount, e.g. =SUM(AD13:AD19)/(1+$B$2) but again, this incorrectly assumes that the vat is the same on every row.

 

[ebea]

I actually provided the answer, not the question. Regardless of that, it looks like you may have misunderstood what was asked. Hopefully this will help you to understand a little better.

If you look at the data sample provided by AllisterB you will see that the percentage is variable by row, which is why sumproduct is needed. Your suggestion assumes that the VAT percentage is the same on every row. In order to produce the correct results the calculation needs to be performed on a row by row basis (using an array) which can not be done the way that you suggest.

Also, =SUM(AD13:AD19)*$B$2 is calculating the result as a percentage of the total amount, where the correct result needs to be calculated on the pre-tax amount, e.g. =SUM(AD13:AD19)/(1+$B$2) but again, this incorrectly assumes that the vat is the same on every row.

What I wrote in Cursive, was to the OP, not to you Jason . Yes, if he has varying VAT I agree, but that's not what I see in he's example. He only set Null or 10%

 

[jasonb75]

If that was the case, you should have preceded your cursive quote with a reference to the OP instead of the pronoun 'you', which implies that you are saying it to the author of the previous reply.
It would appear that a dictionary is the only place where 'clarity' comes before 'confusion'.

He is varying VAT, null / 0% is not the same as 10%. You're taking 10% off of the items that have 0% VAT as well

Simplified version of the OP's data showing the Sumproduct formula and your Sum formula, notice the difference?

Book2
	A	B
1
2	10%	20
3	0%	30
4	10%	40
5
6		90
7		5.454545
8		9
Sheet14

Cell Formulas
Range	Formula
B6	B6	=SUM(B2:B5)
B7	B7	=B6-SUMPRODUCT(B2:B4/(1+$A2:$A4))
B8	B8	=SUM(B2:B4)*$A$2
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ebea]

If that was the case, you should have preceded your cursive quote with a reference to the OP instead of the pronoun 'you', which implies that you are saying it to the author of the previous reply.
It would appear that a dictionary is the only place where 'clarity' comes before 'confusion'.

He is varying VAT, null / 0% is not the same as 10%. You're taking 10% off of the items that have 0% VAT as well

Simplified version of the OP's data showing the Sumproduct formula and your Sum formula, notice the difference?

Book2
	A	B
1
2	10%	20
3	0%	30
4	10%	40
5
6		90
7		5.454545
8		9
Sheet14

Cell Formulas
Range	Formula
B6	B6	=SUM(B2:B5)
B7	B7	=B6-SUMPRODUCT(B2:B4/(1+$A2:$A4))
B8	B8	=SUM(B2:B4)*$A$2
Press CTRL+SHIFT+ENTER to enter array formulas.

The Sum for columns without an amount (january), will also show a 0 in result.
I assume, that the numbers you refer to in the B Column in your example, must be from table 2, and not 3. In Table 3, it's what he receive in amount, and they are inclusive VAT. If they should be in varying amount, (some inclusive, some exclusive), it would be a problem for the accountant. So I see Table 3 as all inclusive VAT (10%)
IF it really should be so, that VAT should differ in Table 3 (different VAT amount), I would personally put a sum for each row, in a Column in the end of Table 3, and calculate it in that way.

 

[ebea]

Thats how I see the received amount, and all inclusive VAT (10% as only option).

Mappe1
	A	B	C	D	E	F
1	Table 3
2		10%
3		Received
4	Source	Jan	Feb	Mar	Apr	May
5	Sales	0	50	100	0	0
6	Sales	0	0	0	0	0
7
8				Text
9	Sales	0	0	220	0	0
10	Sales	0	0	30	0	0
11	Sales	0	80	0	20	0
12
13		0	130	350,00	20	0
14		0	13	35	2	0
Ark2

Cell Formulas
Range	Formula
B13:F13	B13	=SUM(B5:B11)
B14:F14	B14	=SUM(B5:B11)*$B$2

 

[jasonb75]

I would personally put a sum for each row, in a Column in the end of Table 3, and calculate it in that way.

That would require a column for each month which I believe is what the OP is trying to avoid.

I agree that the layout is not ideal in regard to what it does and doesn't contain, personally I would say that to avoid the extra columns something like this might work (I've used 5% instead of 0% for the second amount but it works with anything).

Book2
	A	B	C
1
2	Item 1	10%	20
3	Item 2	5%	30
4	Item 3	10%	40
5
6	Received inc vat	10%	60
7		5%	40
8	Received exc vat	10%	54.55
9		5%	38.10
10	Vat	10%	5.45
11		5%	1.90
Sheet14

Cell Formulas
Range	Formula
C6:C7	C6	=SUMIF($B$2:$B$4,B6,C2:C4)
C8:C9	C8	=C6/(1+B6)
C10:C11	C10	=C6-C8