Using Excel 2000
Hi,
Pairs S5 ToRight
Multiple columns combinations R6 ToDown
Data Columns C:P (P1 To P14)
Cell R6 = 1|2|3|4|5|6|7 mean (P1+P2+P3+P4+P5+P6+P7) count Pairs S5 (1|1|1|1|1|1|1) result S6 = 34
Cell R7 = 1|2|3|4|5|6|8 mean (P1+P2+P3+P4+P5+P6+P8) count Pairs S5 (1|1|1|1|1|1|1) result S7 = 28
Example: I have count using AutoFilter as there are 14 columns so COMBIN(14,7) = 3432 I believe need VBA
Thank you in advance
Regards,
Kishan
Hi,
Pairs S5 ToRight
Multiple columns combinations R6 ToDown
Data Columns C:P (P1 To P14)
Cell R6 = 1|2|3|4|5|6|7 mean (P1+P2+P3+P4+P5+P6+P7) count Pairs S5 (1|1|1|1|1|1|1) result S6 = 34
Cell R7 = 1|2|3|4|5|6|8 mean (P1+P2+P3+P4+P5+P6+P8) count Pairs S5 (1|1|1|1|1|1|1) result S7 = 28
Example: I have count using AutoFilter as there are 14 columns so COMBIN(14,7) = 3432 I believe need VBA
Book1 | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
1 | Jor | Temp | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | P13 | P14 | EM 1 | ||||||
2 | Jor | Temp | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | P13 | P14 | EM 1 | ||||||
3 | Jor | Temp | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | P13 | P14 | EM 1 | ||||||
4 | Jor | Temp | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 | P11 | P12 | P13 | P14 | EM 1 | Multiple | Counts | Counts | Counts | ||
5 | Jor | Temp | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | EM 1 | Columns | 1|1|1|1|1|1|1 | X|X|2|2|1|2|1 | 1|1|1|1|1|1|X | ||
6 | 1 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | X | 1 | 1|2|3|4|5|6|7 | 34 | 1 | 10 | |||
7 | 2 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | X | 1 | 1|2|3|4|5|6|8 | 28 | 7 | ||||
8 | 3 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | 1 | 1 | 1 | X | 1 | 1|2|3|4|5|6|9 | 25 | 15 | ||||
9 | 4 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | X | X | 1|2|3|4|5|6|10 | 28 | 9 | ||||
10 | 5 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | 1 | 1 | 1|2|3|4|5|6|11 | 19 | 22 | ||||
11 | 6 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | X | 1 | 2 | 1 | 1 | 1|2|3|4|5|6|12 | 22 | 14 | ||||
12 | 7 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | X | 2 | 1 | 2 | X | 1 | 1|2|3|4|5|6|13 | 12 | 15 | ||||
13 | 8 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 2 | 1 | 1 | 2 | 1|2|3|4|5|6|14 | 26 | 13 | ||||
14 | 9 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | X | X | 2 | 2 | 2 | X | 1|2|3|4|5|7|8 | ||||||
15 | 10 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 2 | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|5|7|9 | ||||||
16 | 11 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | X | 1 | 2 | 1|2|3|4|5|7|10 | ||||||
17 | 12 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | 2 | 1 | 2 | 2 | 1|2|3|4|5|7|11 | ||||||
18 | 13 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | X | X | 2 | 1 | X | 2 | 1|2|3|4|5|7|12 | ||||||
19 | 14 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | X | 2 | 1 | 1 | 1|2|3|4|5|7|13 | ||||||
20 | 15 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | X | 1 | 2 | 2 | 1|2|3|4|5|7|14 | ||||||
21 | 16 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 2 | 1 | X | 1 | X | 1|2|3|4|5|8|9 | ||||||
22 | 17 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | X | X | X | 1|2|3|4|5|8|10 | ||||||
23 | 18 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | X | X | X | 1 | 1 | 1|2|3|4|5|8|11 | ||||||
24 | 19 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | X | X | X | 1|2|3|4|5|8|12 | ||||||
25 | 20 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|5|8|13 | ||||||
26 | 21 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | X | X | X | 2 | 1 | 1|2|3|4|5|8|14 | ||||||
27 | 22 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|5|9|10 | ||||||
28 | 23 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|5|9|11 | ||||||
29 | 24 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | 1 | X | 1|2|3|4|5|9|12 | ||||||
30 | 25 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | 1 | 2 | 2 | X | 1|2|3|4|5|9|13 | ||||||
31 | 26 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | X | X | 2 | 2 | 1|2|3|4|5|9|14 | ||||||
32 | 27 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | X | 1 | 2 | 2 | 1 | 1|2|3|4|5|10|11 | ||||||
33 | 28 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | X | 2 | 2 | 1 | 1|2|3|4|5|10|12 | ||||||
34 | 29 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | 1 | 2 | 2 | X | 1|2|3|4|5|10|13 | ||||||
35 | 30 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | X | X | 2 | 2 | 1|2|3|4|5|10|14 | ||||||
36 | 31 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | X | X | X | 1 | 1 | 1|2|3|4|5|11|12 | ||||||
37 | 32 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | X | X | X | 1|2|3|4|5|11|13 | ||||||
38 | 33 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|5|11|14 | ||||||
39 | 34 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | 1 | X | 2 | X | 1|2|3|4|5|12|13 | ||||||
40 | 35 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | X | 1 | 1|2|3|4|5|12|14 | ||||||
41 | 36 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | X | 2 | 2 | 2 | 1 | 1|2|3|4|5|13|14 | ||||||
42 | 37 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|6|7|8 | ||||||
43 | 38 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | 1 | X | 1|2|3|4|6|7|9 | ||||||
44 | 39 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | X | 1 | 2 | 1|2|3|4|6|7|10 | ||||||
45 | 40 | 70/71 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | X | 1 | 1 | 2 | 2 | 1|2|3|4|6|7|11 | ||||||
46 | 1 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | X | X | 1|2|3|4|6|7|12 | ||||||
47 | 2 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | 1 | 1 | 1|2|3|4|6|7|13 | ||||||
48 | 3 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | X | 1 | 1|2|3|4|6|7|14 | ||||||
49 | 4 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | 1 | X | 1 | X | 1 | 1|2|3|4|6|8|9 | ||||||
50 | 5 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 2 | X | 1 | X | X | 1|2|3|4|6|8|10 | ||||||
51 | 6 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 2 | 1 | X | 1 | X | 1 | 1|2|3|4|6|8|11 | ||||||
52 | 7 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | X | 2 | X | 2 | 2 | 1 | 1|2|3|4|6|8|12 | ||||||
53 | 8 | 71/72 | 1 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | X | 1 | 2 | 1 | 1|2|3|4|6|8|13 | ||||||
54 | 9 | 71/72 | 2 | 1 | X | 2 | 1 | X | X | X | 1 | X | X | 1 | 2 | X | 1|2|3|4|6|8|14 | ||||||
55 | 10 | 71/72 | X | X | 2 | 1 | X | 1 | 1 | 2 | 1 | 1 | 2 | 1 | 2 | 1 | 1|2|3|4|6|9|10 | ||||||
56 | 11 | 71/72 | 1 | X | 2 | X | 1 | 2 | 1 | 2 | X | X | 1 | 1 | 2 | X | 1|2|3|4|6|9|11 | ||||||
57 | 12 | 71/72 | X | 2 | X | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | X | X | X | 1|2|3|4|6|9|12 | ||||||
58 | 13 | 71/72 | 1 | 1 | X | 1 | 1 | X | 1 | 1 | X | X | 1 | 1 | 1 | 1 | 1|2|3|4|6|9|13 | ||||||
59 | 14 | 71/72 | 1 | 1 | X | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1|2|3|4|6|9|14 | ||||||
60 | 15 | 71/72 | 2 | 1 | X | 1 | 1 | 1 | 2 | 1 | 1 | X | X | X | 1 | X | 1|2|3|4|6|10|11 | ||||||
61 | 16 | 71/72 | X | 1 | 2 | 2 | X | 1 | 1 | X | 1 | 1 | 1 | 1 | 1 | 1 | 1|2|3|4|6|10|12 | ||||||
62 | 17 | 71/72 | 2 | 1 | 1 | 1 | 2 | 2 | 1 | 1 | 2 | 2 | X | X | 1 | 2 | 1|2|3|4|6|10|13 | ||||||
63 | 18 | 71/72 | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 1 | X | 2 | 1 | 1 | 1 | 1|2|3|4|6|10|14 | ||||||
64 | 19 | 71/72 | 1 | X | 2 | 1 | X | 1 | 1 | 2 | 1 | X | 2 | 2 | 1 | X | 1|2|3|4|6|11|12 | ||||||
65 | 20 | 71/72 | 1 | X | 1 | X | 1 | 1 | X | X | 1 | 1 | 1 | X | 1 | 1 | 1|2|3|4|6|11|13 | ||||||
66 | 21 | 71/72 | X | X | X | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1|2|3|4|6|11|14 | ||||||
67 | 22 | 71/72 | X | X | 2 | 2 | 1 | 2 | 1 | X | 1 | 1 | X | 1 | 2 | X | 1|2|3|4|6|12|13 | ||||||
68 | 23 | 71/72 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | 1 | 1|2|3|4|6|12|14 | ||||||
69 | 24 | 71/72 | X | 1 | 1 | 1 | X | 1 | 1 | 2 | 1 | 1 | 2 | 1 | X | X | 1|2|3|4|6|13|14 | ||||||
70 | 25 | 71/72 | 1 | 1 | 1 | 2 | X | 1 | X | 1 | 1 | X | 1 | X | X | 1 | 1|2|3|4|7|8|9 | ||||||
71 | 26 | 71/72 | 1 | X | 1 | 1 | 1 | 2 | X | 1 | X | X | X | 2 | 1 | X | 1|2|3|4|7|8|10 | ||||||
72 | 27 | 71/72 | 1 | 1 | X | 1 | X | X | X | 1 | 1 | 1 | X | 1 | 2 | X | 1|2|3|4|7|8|11 | ||||||
73 | 28 | 71/72 | 1 | X | 1 | X | 1 | X | 2 | 2 | X | 1 | 1 | 1 | 1 | 2 | 1|2|3|4|7|8|12 | ||||||
74 | 29 | 71/72 | 1 | 1 | X | 2 | 2 | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | 2 | 1|2|3|4|7|8|13 | ||||||
75 | 30 | 71/72 | 1 | X | X | 1 | X | 1 | 1 | 1 | 2 | 1 | 1 | 1 | 1 | X | 1|2|3|4|7|8|14 | ||||||
76 | 31 | 71/72 | 1 | X | X | X | X | 1 | 2 | 1 | 1 | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|7|9|10 | ||||||
77 | 32 | 71/72 | 2 | X | 1 | X | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | 2 | 2 | 1|2|3|4|7|9|11 | ||||||
78 | 33 | 71/72 | 1 | 1 | X | 1 | 2 | 2 | 2 | 1 | 1 | X | 1 | X | X | 2 | 1|2|3|4|7|9|12 | ||||||
79 | 34 | 71/72 | 1 | 1 | 1 | 1 | X | 1 | 1 | 1 | 1 | 1 | 1 | X | X | 1 | 1|2|3|4|7|9|13 | ||||||
80 | 35 | 71/72 | 1 | 1 | 1 | 1 | 2 | 1 | X | 1 | 1 | X | 1 | 1 | 1 | 1 | 1|2|3|4|7|9|14 | ||||||
81 | 36 | 71/72 | 2 | X | 2 | X | 1 | 2 | 1 | X | 1 | X | 1 | X | X | 1 | 1|2|3|4|7|10|11 | ||||||
82 | 37 | 71/72 | 2 | 1 | X | 1 | 1 | 1 | 1 | 1 | 2 | X | 1 | 1 | 1 | X | 1|2|3|4|7|10|12 | ||||||
83 | 38 | 71/72 | 1 | 1 | 1 | 1 | X | 1 | X | 1 | 2 | 1 | 1 | 1 | X | 2 | 1|2|3|4|7|10|13 | ||||||
84 | 1 | 72/73 | X | 1 | 2 | 1 | 2 | X | X | 1 | 1 | 2 | X | 1 | 1 | 2 | 1|2|3|4|7|10|14 | ||||||
85 | 2 | 72/73 | 1 | X | 2 | 2 | 2 | 1 | X | X | 2 | 2 | X | X | 1 | 1 | 1|2|3|4|7|11|12 | ||||||
86 | 3 | 72/73 | 1 | X | 1 | 1 | X | X | 2 | 2 | X | X | 1 | 1 | X | X | 1|2|3|4|7|11|13 | ||||||
87 | 4 | 72/73 | 1 | X | 1 | 2 | 2 | 1 | X | 1 | 1 | X | 1 | 2 | 2 | 1 | 1|2|3|4|7|11|14 | ||||||
88 | 5 | 72/73 | X | 1 | X | 1 | X | 1 | 1 | X | 1 | 1 | 1 | X | X | 1 | 1|2|3|4|7|12|13 | ||||||
89 | 6 | 72/73 | X | X | X | 1 | 1 | 1 | 1 | X | 1 | X | 2 | 1 | 2 | X | 1|2|3|4|7|12|14 | ||||||
90 | 7 | 72/73 | 2 | X | 1 | 1 | 1 | 2 | X | X | X | 1 | 1 | 1 | 2 | 1 | 1|2|3|4|7|13|14 | ||||||
Sheet1 |
Thank you in advance
Regards,
Kishan