Counting Rows Between Two Values and Get Average Number of Rows

[chuchuberthelot]

Hi Guys,
So what I want to do is count the number of cells between two cell values.

Cell values change; the first cell value range is between 100 and 101, and the second one is between 101 and 102 and so on.

Another problem I'm having is that I have to do this for each student Id and get the average number of cells between 100 and 101, 101 and 102 and so on.

Can anybody (excel experts) help me please.

Thank you so much!

 

[Eric W]

Welcome to the forum!

In the future, please consider using the XL2BB tool. (See my signature, or the link in the reply box.) When you enter a lot of data in a picture, you force the helpers to manually type in all that data, and many won't bother. With XL2BB, it's just a cut/paste operation.

However, consider this:

Book1
	A	B	C	D	E	F	G	H
1	studentId	date	questionsCovered	examId	correct	total_num_qs	gap	avg_gap
2	1000	172	120	100	55	100	1	2
3	1000	176	120
4	1000	179	100	101	74	120	3
5	1000	181	130
6	1000	183	80
7	1000	184	80
8	1000	186	120	102	62	100	1
9	1000	190	90
10	1000	193	110	103	103	140	1
11	1000	197	130
12	1000	200	110	104	76	100	4
13	1000	201	130
14	1000	203	120
15	1000	204	90
16	1000	206	100
17	1000	207	60	105	95	120	2
18	1000	209	90
19	1000	212	70
20	1000	214	90	106	70	90	4
21	1000	215	120
22	1000	217	80
23	1000	218	110
24	1000	220	110
25	1000	221	90	107	79	100	0
26	1001	50	90	100	62	100	0	1.111111
27	1001	57	90	101	73	120	4
28	1001	58	120
29	1001	60	110
30	1001	61	110
31	1001	63	100
32	1001	64	140	102	72	100	1
33	1001	68	100
34	1001	71	120	103	112	140	0
35	1001	85	110	105	100	120	0
36	1001	92	80	106	76	90	1
37	1001	96	100
38	1001	99	120	107	85	100	2
39	1001	101	80
40	1001	104	100
41	1001	106	130	108	77	90	0
42	1001	113	90	109	99	110	2
43	1001	115	80
44	1001	118	50
Sheet1

Cell Formulas
Range	Formula
G2:G44	G2	=IF(E2<>"",IFERROR(AGGREGATE(15,6,ROW(A3:A20)/(A3:A20=A2)/(E3:E20<>""),1)-ROW(A3),COUNTIF(A3:A20,A2)),"")
H2:H44	H2	=IF(AND(A2<>A1,A2<>""),AVERAGEIF(A:A,A2,G:G),"")

This seems to do what you want. It assumes that the gap will never be more than 18, but that's easy to change if necessary. Let us know.