Excel calculation

[ashani]

Hi experts,

I wonder if someone could please guide me - I'm trying to get percentage from the given formula for different Sellers but when I copy it Excel change the cell value to right next to the first one, however I want excel to work out automatically (leave 3 columns and start from 4th columns and so on - Week wise) I have screenshot the problem, I will really appreciate if you could please guide me either through simple formula or via VBA.

Thank you

 

[Max1616]

Try this:

=IFERROR((OFFSET($H5,0,(COLUMN(A1)*4)-4,1,1)-OFFSET($J5,0,(COLUMN(A1)*4)-4,1,1))/OFFSET($K5,0,(COLUMN(A1)*4)-4,1,1),0)

 

[Fluff]

Another option

=LET(w,WRAPROWS(H4:W4,4),TOROW(IFERROR((INDEX(w,,1)-INDEX(w,,3))/INDEX(w,,4),0)))

 

[Skybluekid]

Try This

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2		Formula	(PR-DT)/(TS)
3			Week 1	Week 2	Week 3	Week 4		Week 1	Week 1	Week 1	Week 1	Week 2	Week 2	Week 2	Week 2	Week 3	Week 3	Week 3	Week 3	Week 4	Week 4	Week 4	Week 4
4								PR	PS	DT	TS	PR	PS	DT	TS	PR	PS	DT	TS	PR	PS	DT	TS
5		Seller 1	100%	#DIV/0!	-100%	25%		1	0	0	1	0	0	0	0	0	0	2	2	2	1	1	4
6		Seller 2	100%	#DIV/0!	0%	-100%		1	0	0	1	0	0	0	0	1	0	1	2	0	0	1	1
7		Seller 3	100%	100%	100%	100%		1	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1
8		Seller 4	33%	#DIV/0!	#DIV/0!	#DIV/0!		2	0	1	3	0	0	0	0	0	0	0	0	0	0	0	0
9		Seller 5	0%	100%	0%	50%		1	0	1	2	2	0	0	2	1	0	1	2	3	0	1	4
10		Seller 6	100%	100%	#DIV/0!	100%		3	0	0	3	2	0	0	2	0	0	0	0	2	0	0	2
11		Seller 7	100%	33%	#DIV/0!	0%		1	0	0	1	2	0	1	3	0	0	0	0	0	1	0	1
12		Seller 8	100%	#DIV/0!	100%	-50%		2	0	0	2	0	0	0	0	1	0	0	1	0	1	1	2
13
Sheet2

Cell Formulas
Range	Formula
C5	C5	=LET(pr,INDEX($H5:$W5,MATCH(C$3&"PR",$H$3:$W$3&$H$4:$W$4,0)),dt,INDEX($H5:$W5,MATCH($C$3&"DT",$H$3:$W$3&$H$4:$W$4,0)),ts,INDEX($H5:$W5,MATCH($C$3&"TS",$H$3:$W$3&$H$4:$W$4,0)),(pr-dt)/ts)
D5:D12	D5	=LET(pr,INDEX($H5:$W5,MATCH(D$3&"PR",$H$3:$W$3&$H$4:$W$4,0)),dt,INDEX($H5:$W5,MATCH($D$3&"DT",$H$3:$W$3&$H$4:$W$4,0)),ts,INDEX($H5:$W5,MATCH($D$3&"TS",$H$3:$W$3&$H$4:$W$4,0)),(pr-dt)/ts)
E5:E12	E5	=LET(pr,INDEX($H5:$W5,MATCH(E$3&"PR",$H$3:$W$3&$H$4:$W$4,0)),dt,INDEX($H5:$W5,MATCH($E$3&"DT",$H$3:$W$3&$H$4:$W$4,0)),ts,INDEX($H5:$W5,MATCH($E$3&"TS",$H$3:$W$3&$H$4:$W$4,0)),(pr-dt)/ts)
F5:F12	F5	=LET(pr,INDEX($H5:$W5,MATCH(F$3&"PR",$H$3:$W$3&$H$4:$W$4,0)),dt,INDEX($H5:$W5,MATCH($F$3&"DT",$H$3:$W$3&$H$4:$W$4,0)),ts,INDEX($H5:$W5,MATCH($F$3&"TS",$H$3:$W$3&$H$4:$W$4,0)),(pr-dt)/ts)
C6:C12	C6	=LET(pr,INDEX(H6:W6,MATCH(C$3&"PR",$H$3:$W$3&$H$4:$W$4,0)),dt,INDEX(H6:W6,MATCH($C$3&"DT",$H$3:$W$3&$H$4:$W$4,0)),ts,INDEX(H6:W6,MATCH($C$3&"TS",$H$3:$W$3&$H$4:$W$4,0)),(pr-dt)/ts)

 

[Fluff]

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2		Formula	(PR-DT)/(TS)					Week 1	Week 1	Week 1	Week 1	Week 2	Week 2	Week 2	Week 2	Week 3	Week 3	Week 3	Week 3	Week 4	Week 4	Week 4	Week 4
3			Week 1	Week 2	Week 3	Week 4		PR	PS	DT	TS	PR	PS	DT	TS	PR	PS	DT	TS	PR	PS	DT	TS
4		Seller 1	100%	0%	-100%	25%		1	0	0	1	0	0	0	0	0	0	2	2	2	1	1	4
5		Seller 2	100%	0%	0%	-100%		1	0	0	1	0	0	0	0	1	0	1	2	0	0	1	1
6		Seller 3	100%	100%	100%	100%		1	0	0	1	1	0	0	1	1	0	0	1	1	0	0	1
7		Seller 4	33%	0%	0%	0%		2	0	1	3	0	0	0	0	0	0	0	0	0	0	0	0
8		Seller 5	0%	100%	0%	50%		1	0	1	2	2	0	0	2	1	0	1	2	3	0	1	4
9		Seller 6	100%	100%	0%	100%		3	0	0	3	2	0	0	2	0	0	0	0	2	0	0	2
10		Seller 7	100%	33%	0%	0%		1	0	0	1	2	0	1	3	0	0	0	0	0	1	0	1
11		Seller 8	100%	0%	100%	-50%		2	0	0	2	0	0	0	0	1	0	0	1	0	1	1	2
Lists

Cell Formulas
Range	Formula
C4:F11	C4	=LET(w,WRAPROWS(H4:W4,4),TOROW(IFERROR((INDEX(w,,1)-INDEX(w,,3))/INDEX(w,,4),0)))
Dynamic array formulas.

 

[ashani]

Thank you so much - it works perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.