Analyze Quartile wise stats Tenure wise

[Maddy29]

Dear Experts,
I wanted to analyze the data from Quartile 2 to Quartile Q3.
How is Individual's productivity against workout in these quartile tenure wise. how much hours is extra worked individually.
Could someone please help me with my request.

Variance Data.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	Y	Z	AA	AB	AC	AD	AE	AF	AG
1	Emp ID	Name	Tenure Year	Tenure Month	Workout	Core Time	Productivity	Quartile 2	Productivity Quartile 2	Quartile 3	Productivity Quartile 3	Workout Q2	Workout Q3	Total WK	CD	I - H	K - J	max/min	Percent		Baseline of Productivuty			120.36%					calculation
2	134	Ovi	4	3	1018.07	845.87	120.36%	114%	120%	116%	120%	1018.07	1018.07	0.00	2yr-<5yr	0.00%	-	120.36%	115.10%	Good				97.73%			Productivity Quartile 2	if(g2<O4,O4,G2)		total workout baseline	workout Q2	sum of workout Q2
3	20	Pat	2	10	1064.99	893.65	119.17%		119%		119%	1064.99	1064.99	0.00	2yr-<5yr	0.00%	-		115.10%	Good							Productivity Quartile 3	if(H2<O5,O4,H2)		sum of workout	workout Q2	sum of workout Q3
4	30	Him	5	9	502.15	421.47	119.14%		119%		119%	502.15	502.15	0.00	5	0.00%	-		115.10%	Good		0	< 6m					Q2	QUARTILE(G2:G26,2)
5	32	Piy	2	7	825.15	697.75	118.34%		118%		118%	825.15	825.15	0.00	2yr-<5yr	0.00%	-		115.10%	Good		6	6m - < 1yr	103.84%	751.06	775.87		Q3	QUARTILE(G2:G26,3)	total coretime
6	131	Ape	3	2	820.11	699.82	117.19%		117%		117%	820.11	820.11	0.00	2yr-<5yr	0.00%	-		115.10%	Good		12	1yr - < 2yr	108.82%	753.27	815.09				sum of coretime
7	22	San	1	11	971.23	835.68	116.22%		116%		116%	971.23	971.23	0.00	1yr - < 2yr	0.00%	-		108.82%	Good		24	2yr-<5yr	115.10%	817.33	940.10	workout Quartile 2	iferror((e2/g2)*H2),"")		total workout at baseline / total core time
8	138	Uth	5	0	1000.58	862.92	115.95%		116%		116%	1000.58	1001.16	0.58	5	0.07%	0.58		113.25%	Good		60	5	113.25%			workout Quartile 3	iferror((e2/g2)*I2),"")	Prod baseline
9	34	Sak	2	4	939.56	811.97	115.71%		116%		116%	939.56	942.06	2.50	2yr-<5yr	0.31%	2.50		115.10%	Good
10	24	Sug	0	10	807.97	705.25	114.57%		115%		116%	807.97	818.18	10.21	6m - < 1yr	1.45%	10.21		103.84%	Good									total prod at Q2	total workout at Q2/total core time
11	33	Rav	8	8	917.66	803.18	114.25%		114%		116%	917.66	931.86	14.20	5	1.77%	14.20		115.10%	Bad									total prod at Q2	total workout at Q3/total core time
12	28	Dee	2	9	861.31	754.28	114.19%		114%		116%	861.31	875.09	13.78	2yr-<5yr	1.83%	13.78		115.10%	Bad		workout baseline - sum of workout
13	139	Var	2	1	971.28	850.97	114.14%		114%		116%	971.28	987.26	15.98	2yr-<5yr	1.88%	15.98		115.10%	Bad		workout @ Q2 - sum of workout @ Q2
14	27	Ben	1	6	472.4	415.18	113.78%		114%		116%	473.15	481.69	8.54	1yr - < 2yr	2.06%	8.54		108.82%	Good		workout @ Q3 - sum of workout @ Q3										% Variance	workout variance
15	133	Niv	2	11	1108.23	978.27	113.28%		114%		116%	1114.88	1135.01	20.13	2yr-<5yr	2.06%	20.13		115.10%	Bad		Total Coretime - sum of Coretime
16	35	Smi	6	2	907.71	807.12	112.46%		114%		116%	919.82	936.42	16.61	5	2.06%	16.61		115.10%	Bad		total productivity baseline = sum of workout/sum of coretime										0.00%
17	31	Om	9	0	892.36	797.5	111.89%		114%		116%	908.87	925.28	16.41	5	2.06%	16.41		115.10%	Bad		Total productivity @ Q2 = sum of workout @ Q2 / sum of coretime										0.00%
18	37	Yas	1	1	902.51	830.92	108.62%		114%		116%	946.88	963.97	17.10	1yr - < 2yr	2.06%	17.10		108.82%	Bad		Total productivity @ Q2 = sum of workout @ Q3 / sum of coretime										0.00%
19	19	Log	5	8	834.71	788.82	105.82%		114%		116%	898.92	915.15	16.23	5	2.06%	16.23		115.10%	Bad												0.00%
20	36	Shi	1	1	845.05	807.77	104.62%		114%		116%	920.49	937.11	16.62	1yr - < 2yr	2.06%	16.62		108.82%	Bad												0.00%
21	135	Pra	0	10	649.51	621	104.59%		114%		116%	707.70	720.48	12.78	6m - < 1yr	2.06%	12.78		103.84%	Good												0.07%
22	129	Ana	4	11	852.18	823.4	103.50%		114%		116%	938.30	955.24	16.94	2yr-<5yr	2.06%	16.94		115.10%	Bad												0.31%
23	25	Sur	1	1	884.27	876.8	100.85%		114%		116%	999.22	1017.26	18.04	1yr - < 2yr	2.06%	18.04		108.82%	Bad
24	21	Rag	0	8	797.79	810.08	98.48%		114%		116%	923.19	939.86	16.67	6m - < 1yr	2.06%	16.67		103.84%	Bad
25	26	Swa	0	11	848.19	867.9	97.73%		114%		116%	989.05	1006.91	17.86	6m - < 1yr	2.06%	17.86	97.73%	103.84%	Bad
26												21440.53	21691.69	251.16
27					20694.97	18607.57	111.22%					745.56	996.72	251.16
Sample Sheet

Cell Formulas
Range	Formula
H2	H2	=QUARTILE($G$2:$G$25,2)
J2	J2	=QUARTILE($G$2:$G$25,3)
K2:K25	K2	=IF(I2<$J$2,$J$2,I2)
L2:L25	L2	=IFERROR(((E2/G2)*I2),"")
M2:M25	M2	=IFERROR(((E2/G2)*K2),"")
N27,N2:N25	N2	=M2-L2
O2:O25	O2	=LOOKUP(C2*12+D2,V$4:V$8,W$4:W$8)
P2:P25	P2	=K2-I2
Q2:Q25	Q2	=M2-L2
R2:R25	R2	=IF(G2=X$1,X$1,IF(G2=X$2,X$2,""))
S9:S25,S2:S7	S2	=VLOOKUP(C2*12+D2,V$4:X$7,3,1)
T2:T25	T2	=IF(G2>VLOOKUP(C2*12+D2,V$4:Z$7,3),"Good","Bad")
S8	S8	=VLOOKUP(C8*12+D8,V$4:X$8,3,1)
X1	X1	=MAX(G2:G25)
X2	X2	=MIN(G2:G25)
Y5:Y7	Y5	=IFERROR(AVERAGEIF(O$2:O$25,W5,F$2:F$25),"")
Z5:Z7	Z5	=IFERROR(AVERAGEIF(O$2:O$25,W5,E$2:E$25),"")
X12,X4:X8	X4	=IFERROR(AVERAGEIF(O$2:O$25,W4,G$2:G$25),"")
AF16:AF22	AF16	=K3-I3
I2:I25	I2	=IF(G2<$H$2,$H$2,G2)
L26:N26	L26	=SUM(L2:L25)
L27	L27	=L26-E27
M27	M27	=M26-E27
E27:F27	E27	=SUM(E2:E25)
G27	G27	=E27/F27

 

[Maddy29]

can someone pls help

 

[Maddy29]

I would really appreciate if someone can reply or suggest.

 

[Maddy29]

is it possible for experts to reply. any help would be great help

 

[Maddy29]

dear experts thanks for showing your expertise. really appreciate.