selection specific ranges to average

[jbrown021286]

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6
7
8							208
9							14-Dec	20-Dec
10						Carl	206	206	210
11						Jeffrey	208	208	208
12						Brandun	111	111	130
13						Israel	6	12	25
14						Kedrin	23	25	40
15						Brayan	11	20	30
16						Justin	208	208	208
17						Venro	174	176	180
18						Angel	206	206	208
19						Ramy	205	205	208
20						Tre	29	35	60
21						Miguel	206	206	208
22						Joshua	107	121	130
23						Patric	24	28	40
24						Gerardo	38	38	60
25						Aldo	98	101	120
26						Tevin	34	37	50
27
28
29
30
31
32
33
34
35
36
Sheet1

i am wanting to track an increase in numbers week by week. each week i will be adding a collum of numbers and want to be able to subtract the lowest number in a row by each persons name (which should be column H ) from the highest number (which should always be the furthest filled column on the right) then average that number by how many columns are populated in a row. so in this example in cell F10 i would want J10-H10 then divide it by the full number of filled cells in the row 3 to get 1.3333 so i can see the average weekly progress as i add weeks to the data set. is there a good way to achieve this?

 

[Fluff]

How about

Fluff.xlsm
	G	H	I	J	K
8		208
9		45640	45646
10	Carl	206	206	210	1.33
11	Jeffrey	208	208	208	0.00
12	Brandun	111	111	130	6.33
13	Israel	6	12	25	6.33
14	Kedrin	23	25	40	5.67
15	Brayan	11	20	30	6.33
16	Justin	208	208	208	0.00
17	Venro	174	176	180	2.00
18	Angel	206	206	208	0.67
19	Ramy	205	205	208	1.00
20	Tre	29	35	60	10.33
21	Miguel	206	206	208	0.67
22	Joshua	107	121	130	7.67
23	Patric	24	28	40	5.33
24	Gerardo	38	38	60	7.33
25	Aldo	98	101	120	7.33
26	Tevin	34	37	50	5.33
Data

Cell Formulas
Range	Formula
K10:K26	K10	=BYROW(H10:J26,LAMBDA(br,(MAX(br)-MIN(br))/COUNT(br)))
Dynamic array formulas.

 

[jbrown021286]

How about

Fluff.xlsm
	G	H	I	J	K
8		208
9		45640	45646
10	Carl	206	206	210	1.33
11	Jeffrey	208	208	208	0.00
12	Brandun	111	111	130	6.33
13	Israel	6	12	25	6.33
14	Kedrin	23	25	40	5.67
15	Brayan	11	20	30	6.33
16	Justin	208	208	208	0.00
17	Venro	174	176	180	2.00
18	Angel	206	206	208	0.67
19	Ramy	205	205	208	1.00
20	Tre	29	35	60	10.33
21	Miguel	206	206	208	0.67
22	Joshua	107	121	130	7.67
23	Patric	24	28	40	5.33
24	Gerardo	38	38	60	7.33
25	Aldo	98	101	120	7.33
26	Tevin	34	37	50	5.33
Data

Cell Formulas
Range	Formula
K10:K26	K10	=BYROW(H10:J26,LAMBDA(br,(MAX(br)-MIN(br))/COUNT(br)))
Dynamic array formulas.

shop training.xlsx
	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
4
5					Total number of self studies				Total number of maintenance & repair self studies
6					208				162
7
8	team leader	Average per week	Tech	14-Dec	20-Dec	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15	Column16	Column17	Column18	Column19	Column20
9	Carl	#SPILL!	Carl	206	206
10	Jeffrey	#SPILL!	Jeffrey	208	208
11	Justin	#SPILL!	Brandun	111	111
12	Justin	#SPILL!	Israel	6	12
13	Carl	#SPILL!	Kedrin	23	25
14	Carl	#SPILL!	Brayan	11	20
15	Justin	#SPILL!	Justin	208	208
16	Justin	#SPILL!	Venro	174	176
17	Carl	#SPILL!	Angel	206	206
18	Jeffrey	#SPILL!	Ramy	205	205
19	Carl	#SPILL!	Tre	29	35
20	Miguel	#SPILL!	Miguel	206	206
21	Jeffrey	#SPILL!	Joshua	107	121
22	Miguel	#SPILL!	Patric	24	28
23	Justin	#SPILL!	Gerardo	38	38
24	Jeffrey	#SPILL!	Aldo	98	101
25	Miguel	#SPILL!	Tevin	34	37
26
27
Sheet1

Cell Formulas
Range	Formula
F9:F25	F9	=BYROW(H9:AB25,LAMBDA(br,(MAX(br)-MIN(br))/COUNT(br)))

that did work but i was also planning on applying a table so i could filter the data. once i did that i gave a sipll error. is there any way to adapt you formula to work with a table?

 

[Fluff]

You don't need to convert the range into a table, just to use the autofilter. It works just as well on a normal range.
That said you can just use

=(MAX(H9:AB9)-MIN(H9:AB9))/COUNT(H9:AB9)

 

[jbrown021286]

You don't need to convert the range into a table, just to use the autofilter. It works just as well on a normal range.
That said you can just use

=(MAX(H9:AB9)-MIN(H9:AB9))/COUNT(H9:AB9)

that got it. you are awesome thank you for all your help

 

[Fluff]

You're welcome & thanks for the feedback.