# excelchart for PAIRS

#### wd8ekd

##### Board Regular
Hi
By any chance could we get a
excel formula for crossing off
pairs and leaving 4 PAIRS left out of
100 draw numbers??---We use only 45 PAIRS--(no reverse pairs)
We STOP as soon as 4 PAIRS are LEFT---
example ---all 100 draws are put in
order 123 ---say that 486 was drawn
we puy it 468 then we make the pairs

100 Draws-----pairs
468-------------46-48-68
677-------------67-77
023-------------02-03-23
ECT---

all numbers are in order..
doubles we use only two sets of pairs..
so we cross off 46-48-68 till we
get down to 4 PAIR LEFT in the
caart below---

00 01 02 03 04 05 06 07 08 09
11 12 13 14 15 16 17 18 19
22 23 24 25 26 27 28 29
33 34 35 36 37 38 39
44 45 46 47 48 49
55 56 57 58 59
66 67 68 69
77 78 79
88 89
99

hope we explained it right..
dont know if it needs a macro formula
or just a excel formula..
thank you
bob from Michigan

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Rijnsent

##### Well-known Member
Hi Bob,

hereby some functions to get you rolling:
A2: =TEXT(RANDBETWEEN(0,999),"000") -> this will give you a number from 000 to 999, just put it e.g. in A2 and drag it down
After that it would be easy to do this:
B2: =LEFT(A2,2)
C2: =RIGHT(A2,2)
D2: =LEFT(A2,1)&RIGHT(A2,1)
Unfortunately, that won't work, as you want the lowest digit first. The formulas get a bit more complicated, but that would be:

B2: =MIN(MID(\$A2,1,1),MID(\$A2,2,1))&MAX(MID(\$A2,1,1),MID(\$A2,2,1))
C2: =MIN(MID(\$A2,2,1),MID(\$A2,3,1))&MAX(MID(\$A2,2,1),MID(\$A2,3,1))
D2: =MIN(MID(\$A2,1,1),MID(\$A2,3,1))&MAX(MID(\$A2,1,1),MID(\$A2,3,1))

Now you have numbers that can be crossed off. After that you'd need to count if that was the first time that number popped up:

F2: =COUNTIF(\$B\$2:\$D2,B2)-COUNTIF(C2:\$E2,B2) -> drag to F2:H2 and down.
If that number is a 1, that is the first time that number pops up, otherwise it can be ignored. Now you could try to check it off, but you could also count the number of 1s, as that tells you how many unique numbers have passed by...

J2: =COUNTIF(\$F\$2:\$H2,1)
Drag down... You'll notice the numbers going up... As there are 55 possibilities (10+9+8+7 etc ), your line is the one with 51 (could be 52 or 53 if there are more matches in that last line).

Curious to know what you'll do with that data!

Cheers,

Koen

Replies
23
Views
790
Replies
4
Views
541
Replies
4
Views
970
Replies
5
Views
466
Replies
9
Views
400

1,191,580
Messages
5,987,446
Members
440,097
Latest member
Wint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back