Excell spreadsheet formula for binomial exact test calculations

The Excell© worksheet cell entry that calculates alpha for the binomial exact test is as follows:

=1-IF(B2=0,0,BINOMDIST(B2-1, B2+E2, C2/(C2+F2),TRUE))

Using trouble report rates as an example rate performance measure, column B contains "Cnum1," the number of CLEC troubles; column C contains "N_c" the number of lines, column E contains "Inum," the number of ILEC troubles; and column F contains N_i, the number of ILEC lines. The above formula is the cell entry for the first row of performance results in the spreadsheet (row 2) presented on the next page. The data is hypothetical data for demonstration purposes only.

Excell spreadsheet: Hypothetical data example

of binomial exact test calculations.

A

B

C

D

E

F

G

H

1

Measure

Cnum1

N_c

Cval

Inum

N_i

Ival

_

2

15

0

143

0.00%

987

1876543

0.05%

1.00

3

15

3

343

0.86%

4321

2012345

0.20%

0.04

4

15

1

432

0.22%

1321

2012345

0.07%

0.25

5

15

4

876

0.45%

4321

2012345

0.20%

0.12

6

15

2

2987

0.07%

3210

2101234

0.15%

0.94

7

15

6

4321

0.14%

2432

2101234

0.11%

0.38

8

15

5

5432

0.08%

2765

1876543

0.15%

0.90

9

15

7

13210

0.05%

1765

2012345

0.09%

0.94

10

15

8

13210

0.06%

4321

2012345

0.20%

1.00

11

16

0

4

0.00%

32

14321

0.21%

1.00

12

16

3

12

25.00%

876

7654

10.66%

0.16

13

16

2

13

15.38%

987

43210

2.20%

0.04

14

16

8

21

40.00%

876

7654

10.66%

0.00

15

16

1

21

4.55%

1231

48765

2.56%

0.41

16

16

3

76

3.90%

876

7654

10.66%

0.99

17

16

9

98

9.38%

543

21012

2.65%

0.00

18

16

6

132

4.62%

12101

543210

2.32%

0.08

19

16

7

187

3.83%

8987

432101

1.96%

0.10

20

16

4

198

2.06%

10123

498765

2.12%

0.57

21

16

5

365

1.39%

11012

454321

2.45%

0.94

22

19

0

1

0.00%

2799

54321

4.91%

1.00

23

19

2

18

11.11%

1012

321012

0.35%

0.00

24

19

1

54

1.82%

1012

321012

0.34%

0.16

25

19

8

54

13.56%

2987

65432

4.89%

0.00

26

19

7

87

7.95%

2987

65432

4.86%

0.11

27

19

0

87

0.00%

26543

3432101

0.72%

1.00

28

19

5

321

1.61%

987

301234

0.31%

0.00

29

19

9

876

1.09%

1876

210123

0.90%

0.38

30

19

4

987

0.44%

26543

3654321

0.72%

0.93

31

19

6

1210

0.47%

143210

12345678

1.34%

0.99


	Excell spreadsheet: Hypothetical data example
	of binomial exact test calculations.


	A	B	C	D	E	F	G	H
1	Measure	Cnum1	N_c	Cval	Inum	N_i	Ival	_
2	15	0	143	0.00%	987	1876543	0.05%	1.00
3	15	3	343	0.86%	4321	2012345	0.20%	0.04
4	15	1	432	0.22%	1321	2012345	0.07%	0.25
5	15	4	876	0.45%	4321	2012345	0.20%	0.12
6	15	2	2987	0.07%	3210	2101234	0.15%	0.94
7	15	6	4321	0.14%	2432	2101234	0.11%	0.38
8	15	5	5432	0.08%	2765	1876543	0.15%	0.90
9	15	7	13210	0.05%	1765	2012345	0.09%	0.94
10	15	8	13210	0.06%	4321	2012345	0.20%	1.00
11	16	0	4	0.00%	32	14321	0.21%	1.00
12	16	3	12	25.00%	876	7654	10.66%	0.16
13	16	2	13	15.38%	987	43210	2.20%	0.04
14	16	8	21	40.00%	876	7654	10.66%	0.00
15	16	1	21	4.55%	1231	48765	2.56%	0.41
16	16	3	76	3.90%	876	7654	10.66%	0.99
17	16	9	98	9.38%	543	21012	2.65%	0.00
18	16	6	132	4.62%	12101	543210	2.32%	0.08
19	16	7	187	3.83%	8987	432101	1.96%	0.10
20	16	4	198	2.06%	10123	498765	2.12%	0.57
21	16	5	365	1.39%	11012	454321	2.45%	0.94
22	19	0	1	0.00%	2799	54321	4.91%	1.00
23	19	2	18	11.11%	1012	321012	0.35%	0.00
24	19	1	54	1.82%	1012	321012	0.34%	0.16
25	19	8	54	13.56%	2987	65432	4.89%	0.00
26	19	7	87	7.95%	2987	65432	4.86%	0.11
27	19	0	87	0.00%	26543	3432101	0.72%	1.00
28	19	5	321	1.61%	987	301234	0.31%	0.00
29	19	9	876	1.09%	1876	210123	0.90%	0.38
30	19	4	987	0.44%	26543	3654321	0.72%	0.93
31	19	6	1210	0.47%	143210	12345678	1.34%	0.99