Specific range by week number

[Mikkelelgaard]

Hi all! I am currently working on some sales data where I have to calculate the standard deviation and average of each item. However, I only want to include the weeks where the product has been sold (so from the first week with sales to the last week with sales).
With MATCH and LOOKUP I have managed to find the week numbers, but what I struggle with is how I can use it as a range in AVERAGE and STDEV.S?

Hope someone has the answer

Help please.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	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1	Row Labels	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	First Sale	Last sale	First week with sales	Last week with sales	StDev	Average
2	1	0	50	17	151	30	110	91	0	30	0	0	0	90	0	0	0	2	21	1	51	20	10	21	11	20	60	10	40	100	22	110	42	10	26	80	62	80	12	22	70	92	361	60	50	40	0	0	0	0	444	400	30	0	50	30	2	52	93.3778705	55.6415094
3	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	200	0	2200	3051	265	200	110	405	230	431	35	0	380	130	101	52	112	20	200	20	35	53	513.234985	153.245283
4	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	100	100	0	2200	2981	495	0	130	535	510	11	30	0	240	2931	3756	4145	2208	385	100	385	35	53	994.085782	391.641509
5	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	200	0	2200	2211	275	0	100	586	530	241	93	10	60	0	250	325	300	730	200	730	35	53	440.206204	156.811321
6	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	200	0	0	2020	1616	0	510	290	20	820	133	260	40	40	3	20	20	0	200	20	35	52	370.932854	116.830189
7	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	200	0	0	2160	1565	0	510	450	230	800	44	60	30	801	177	459	20	0	200	20	35	52	395.171547	145.396226
8	7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	200	200	0	0	1713	1625	0	500	290	193	540	33	70	30	414	131	180	0	0	200	180	35	51	335.104264	115.45283
9	8	1	60	0	0	1	0	20	0	5	0	0	0	0	20	0	0	10	0	0	2	0	0	0	0	0	0	0	0	0	0	10	1	0	0	0	10	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	1	1	1	50	9.15453523	2.66037736
10	9	1	110	0	0	143	0	0	105	19	20	10	30	0	20	0	0	10	10	0	5	21	2	10	45	0	2	0	60	0	0	18	1	0	35	0	20	0	0	59	0	10	17	60	20	1	6	0	0	0	1	6	0	0	1	6	1	51	30.2289762	16.5471698
11	10	0	132	0	0	36	20	10	20	15	10	0	0	0	20	0	0	10	0	0	2	1	0	0	0	0	0	0	1	0	0	10	1	0	10	0	0	0	0	50	0	0	0	10	0	4	1	0	3	0	0	10	0	0	132	10	2	51	19.9569384	7.09433962
Sheet1

Cell Formulas
Range	Formula
BC2:BC11	BC2	=INDEX(B2:BB2,MATCH(TRUE,B2:BB2>0,0))
BD2:BD11	BD2	=LOOKUP(9.999999999999E+307,IF(B2:BB2>0,B2:BB2))
BE2:BE11	BE2	=MATCH(BC2,B2:BB2,0)
BF2:BF11	BF2	=LOOKUP(2,1/(B2:BB2=BD2),$B$1:$BB$1)
BG2:BG11	BG2	=STDEV.S(B2:BB2)
BH2:BH11	BH2	=AVERAGE(B2:BB2)

 

[Fluff]

How about

=STDEV.S(INDEX(B2:BB2,BE2):INDEX(B2:BB2,BF2))

 

[Mikkelelgaard]

How about

=STDEV.S(INDEX(B2:BB2,BE2):INDEX(B2:BB2,BF2))

Can't believe it was this simple Thanks for saving me!

 

[Fluff]

You're welcome & thanks for the feedback.