Missing numbers

[ACCA369]

Samples
29
32
9
5
25
12
13
24
16
14
0
22
3
7
12
19
5

Above sample data in Excel sheet 1 , column A:A

1) Need to find unique number in the samples and check missing number between 0 and 36

 

[Peter_SSs]

OFFSET is a volatile function so I would avoid it where reasonable alternatives are available. In this case you could consider these alternatives. I have used a helper cell (G2) to keep the formulas a bit shorter & more efficient. That column could be hidden if you want or the formulas could be written without using the helper if there was some particular reason for that.

ACCA369.xlsm
	A	B	C	D	E	F	G
1	Samples		Unique	Missing	Last N	Average	1st Index
2	29		0	1	10	12.2	8
3	32		3	2
4	9		5	4
5	5		7	6
6	25		12	8
7	12		14	9
8	13		16	10
9	24		19	11
10	16		22	13
11	14		24	15
12	0			17
13	22			18
14	3			20
15	7			21
16	12			23
17	19			25
18	5			26
19				27
20				28
21				29
22				30
23				31
24				32
25				33
26				34
27				35
28				36
29
30
31
32
33
34
35
36
37
38
Sheet3

Cell Formulas
Range	Formula
C2:C38	C2	=IFERROR(AGGREGATE(15,6,INDEX(A$2:A$500,G$2):INDEX(A$2:A$500,E$2+G$2-1)/ISNA(MATCH(INDEX(A$2:A$500,G$2):INDEX(A$2:A$500,E$2+G$2-1),C$1:C1,0)),1),"")
D2:D38	D2	=IFERROR(AGGREGATE(15,6,(ROW(INDEX(A:A,1):INDEX(A:A,37))-1)/ISNA(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,37))-1,C$2:C$18,0)),ROWS(D$2:D2)),"")
F2	F2	=AVERAGE(INDEX(A2:A500,G2):INDEX(A2:A500,COUNT(A2:A500)))
G2	G2	=COUNT(A$2:A$500)-E$2+1

 

[ACCA369]

Thank you so much ?

 

[Peter_SSs]

You're welcome.

 

[Sanjeev1976]

Thank you somuch Sir

You're welcome

 

[Dossfm0q]

This One?

Great alz you have simple formula Without any complications

and this just repeat
"(ROW(INDIRECT("1:37"))-1)" to avoid Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B	C	D
1	Samples		Unique	Missing
2	29		0	1
3	32		3	2
4	9		5	4
5	5		7	6
6	25		9	8
7	12		12	10
8	13		13	11
9	24		14	15
10	16		16	17
11	14		19	18
12	0		22	20
13	22		24	21
14	3		25	23
15	7		29	26
16	12		32	27
17	19			28
18	5			30
19				31
20				33
21				34
22				35
23				36
Sheet1

Cell Formulas
Range	Formula
C2:C23	C2	=IFERROR(AGGREGATE(15,6,($A$2:$A$18)/(FREQUENCY(MATCH($A$2:$A$18,$A$2:$A$18,0),MATCH($A$2:$A$18,$A$2:$A$18,0))>0),ROWS($C$2:C2)),"")
D2:D23	D2	=IFERROR(AGGREGATE(15,6,(ROW(INDIRECT("1:37"))-1)/(COUNTIFS($A$2:$A$18,(ROW(INDIRECT("1:37"))-1))=0),ROWS($D$2:D2)),"")