excelchart for PAIRS

wd8ekd

Board Regular
Joined
Sep 12, 2008
Messages
64
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top