Welcome Guest
You last visited April 29, 2017, 5:19 pm
All times shown are
Eastern Time (GMT-5:00)

# help with Excel!

Topic closed. 9 replies. Last post 8 years ago by jm6256.

 Page 1 of 1
New Member
New York
United States
Member #7355
September 30, 2004
19 Posts
Offline
 Posted: May 3, 2009, 1:45 am - IP Logged

can anyone help me with 2 problems

1) if cell A has 638  which formula can I use to sort it (368)

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

thanks

Jim

New Mexico
United States
Member #58526
February 18, 2008
683 Posts
Offline
 Posted: May 3, 2009, 3:18 am - IP Logged

can anyone help me with 2 problems

1) if cell A has 638  which formula can I use to sort it (368)

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

thanks

Jim

Hi, jm, let's see if I can point you in the right direction....

1) if cell A has 638  which formula can I use to sort it (368)

You have to break the # (638) down to it's individual numbers and then sort them small to large (at least that's what it sounds like you want)

-the MID function in excel can select individual characters from a cell, ie. MID(A1,1,1)......would return the 1st character from cell A1 and it would only return that one where MID(A1,2,2) would return the characters starting at character two and return the 3 and the 8

-the LARGE or SMALL function can be used to sort numbers

Let's say cell A1 has the 638

in cell B1 enter the formula =MID(A1,1,1)        [should return a 6]

in cell C1 enter the formula =MID(A1,2,1)        [should return a 3]

in cell D1 enter the formula =MID(A1,3,1)        [should return a 8]

then in cell E1 enter the formula =SMALL(B1:D1,1)&SMALL(B1:D1,2)&SMALL(B1:D1,3)     [should return 368]

hope this helps!!!!

New Mexico
United States
Member #58526
February 18, 2008
683 Posts
Offline
 Posted: May 3, 2009, 3:20 am - IP Logged

can anyone help me with 2 problems

1) if cell A has 638  which formula can I use to sort it (368)

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

thanks

Jim

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

I use the MATCH function to look through a column of numbers and match what I lookin' for......look in Excel's help files for the MATCH function and it should help you out

New Member
New York
United States
Member #7355
September 30, 2004
19 Posts
Offline
 Posted: May 3, 2009, 7:28 am - IP Logged

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

I use the MATCH function to look through a column of numbers and match what I lookin' for......look in Excel's help files for the MATCH function and it should help you out

thanks Texas MadMan i'll give it a try

United States
Member #41846
June 23, 2006
462 Posts
Offline
 Posted: May 3, 2009, 8:18 am - IP Logged

JM

am I correct in that you want to find any previous box match for a specific #, and that changing the order from low to high was just a step in the process?   I do this with a VBA if you want to consider a vba solution

p8

Pennsylvania
United States
Member #2218
September 1, 2003
5464 Posts
Offline
 Posted: May 3, 2009, 11:11 am - IP Logged

can anyone help me with 2 problems

1) if cell A has 638  which formula can I use to sort it (368)

2) how would you figure out longest day out for a number in excel. (not frequency how many time it was drawn) but how long has it been since last drawn.

thanks

Jim

=MATCH(\$A1,INDIRECT("K20:K120"),0)-0

Ok,  if you have the combination 368 in cell A1 and all your past results in cell range K20 through K120 (see formula above).  You can increase K120 depending how many lines of past history draws.  For example you could have a cell range of K20:K5000.  The "K" = cell range is where you have your 3 digit combination results.

Depending on what version of Excel you are using.  I am using 2002.

I use a free add-in for Excel called Morefunc.

http://xcell05.free.fr/morefunc/english/

(Should be at the bottom of the page)

Open Excel and go to Tools > Addins and make sure Morefunc has a checkmark next to it.

This is the formula I use for changing a straight combination into a boxed version

--MCONCAT(VSORT(MID(E20,INTVECTOR(LEN(E20),1),1)+0,,1))

Whwn you enter the above formula you will need to use Ctrl - Shift - Enter on your keyboard.

In the above formula E20 is where the actual drawing combination is located.  In cell D20 I entered the above formula to give me the ascending boxed form.

For example.  If E20 was 931 then in D20 the result would be 139.

Pennsylvania
United States
Member #2218
September 1, 2003
5464 Posts
Offline
 Posted: May 3, 2009, 11:26 am - IP Logged

=MATCH(\$A1,INDIRECT("K20:K120"),0)-0

Ok,  if you have the combination 368 in cell A1 and all your past results in cell range K20 through K120 (see formula above).  You can increase K120 depending how many lines of past history draws.  For example you could have a cell range of K20:K5000.  The "K" = cell range is where you have your 3 digit combination results.

Depending on what version of Excel you are using.  I am using 2002.

I use a free add-in for Excel called Morefunc.

http://xcell05.free.fr/morefunc/english/

(Should be at the bottom of the page)

Open Excel and go to Tools > Addins and make sure Morefunc has a checkmark next to it.

This is the formula I use for changing a straight combination into a boxed version

--MCONCAT(VSORT(MID(E20,INTVECTOR(LEN(E20),1),1)+0,,1))

Whwn you enter the above formula you will need to use Ctrl - Shift - Enter on your keyboard.

In the above formula E20 is where the actual drawing combination is located.  In cell D20 I entered the above formula to give me the ascending boxed form.

For example.  If E20 was 931 then in D20 the result would be 139.

I should have added this in the previous post.  When you are using the Match formula for finding how many drawings since a combination hit, the result from the formula may reult in a "NA" because of the limited number of past drawings in your history range.  Especialy straight combinations.  Some states may have a straight combination out 6000 or more draws.  For boxed combinations you would need probably a smaller history.

You could use this formula in cell B1:

=IF(ISNA(MATCH(A\$1,INDIRECT("K20:X120"),0)-0),100,MATCH(A\$1,INDIRECT("K20:K120"),0)-0)

K20:K120 would mean that you only have 119 past draws in your history.  Of course this would be too small.  You can change the formula above.

What the above formula will do is try to match the combination in cell A1 with the the results in cell range K20:K12.  If the combination is NOT found instead of a "NA" in cell B1 the formula will enter a 100 in cell B1 because you only have 100 past draws to calculate.  The more history you have, you can increase The K120 and also change the "100" in the formula above.  At least this will eliminate the "NA"'s

New Member
New York
United States
Member #7355
September 30, 2004
19 Posts
Offline
 Posted: May 3, 2009, 11:36 am - IP Logged

I should have added this in the previous post.  When you are using the Match formula for finding how many drawings since a combination hit, the result from the formula may reult in a "NA" because of the limited number of past drawings in your history range.  Especialy straight combinations.  Some states may have a straight combination out 6000 or more draws.  For boxed combinations you would need probably a smaller history.

You could use this formula in cell B1:

=IF(ISNA(MATCH(A\$1,INDIRECT("K20:X120"),0)-0),100,MATCH(A\$1,INDIRECT("K20:K120"),0)-0)

K20:K120 would mean that you only have 119 past draws in your history.  Of course this would be too small.  You can change the formula above.

What the above formula will do is try to match the combination in cell A1 with the the results in cell range K20:K12.  If the combination is NOT found instead of a "NA" in cell B1 the formula will enter a 100 in cell B1 because you only have 100 past draws to calculate.  The more history you have, you can increase The K120 and also change the "100" in the formula above.  At least this will eliminate the "NA"'s

thanks winsumloosesum, I have history from 2002. which is why I was looking for a way to look into numbers of days since a number was drawn (probably will use it for box and double) for now.

United States
Member #41846
June 23, 2006
462 Posts
Offline
 Posted: May 3, 2009, 1:21 pm - IP Logged

JM

heres a vba solution. put your numbers to be checked in row 1 col's EFG all boxed or str comb will be displayed startin in row 4 then exact order will be displayed starting in col E

I always recommend you copy your data to a new name, then close the original and work with the copy. if you don't like the results just delete and all is well.

I apologise if you already know all of this, but untill recently I could not comprehend these first few steps and wasted years continuing to work in DOS and GWBasic

with spreadsheet open press alt F8. type the name you want to use for this program in line below macro name. lets assume test then click on create. you will now have a window with the following lines

Sub test()

End Sub

to run the file press alt F8 then select the file name if not already selected then click run and program executes. if you want to change some part of the program press alt F11 and you can see the code. as long as you are in your copy, change any thing you want to see what effect it has. always save you data.

good luck

p8

' Sub prev_box_hits() name i used

' target numbers in col a assumed compressed as 123 not 1 2 3

' steps

' dim some variables

' set up loops

'decompress #'s

' store target number in array

' place next # to be tested in a different array

' compare the two arrays if all #'s match then it is a box match

' subtract target # location (row #) from # to be tested row # put difference in col c or your choice

'

mt = 4 'start matches at row 4

mx = 4000 ' change to the # of records your database has+a few hundred

Dim tn(9), tar(9), dn(4000, 3) ' change to match variable mx

' tn=test # tar=target # dn decompressed #'s mx sets of 3 #'s

GoSub decompress 'convert to single # and store in memory for fast access

' all the digits are now in dn(x,y)

'For x = 1 To mx: For y = 1 To 3: Cells(x, y + 4) = dn(x, y): Next y: Next x

'use above line if you want to verify #'s were converted corectly

'numbers to be tested in row 1 col 5,6,7or E,F,G

For Z = 0 To 9: tar(Z) = 0: Next Z 'zero out array

For y = 5 To 7: a = Cells(1, y)

tar(a) = tar(a) + 1

Next y ' you have loaded array tar() with a count for each target #

For y = 0 To 9: Cells(3, y + 2) = tar(y): Next y

For w = 1 To mx 'loop thru remaining data look for match

For Z = 0 To 9: tn(Z) = 0: Next Z ' zero this array before every test

For y = 1 To 3: a = dn(w, y)

tn(a) = tn(a) + 1

Next y ' you have loaded array tn() with a count for each target #

GoSub compare ' you could do this in line i think its easier to follow this way

Next w ' complete this loop

Exit Sub 'exit this program all below is called from above

decompress:

For x = 1 To mx

Z = Cells(x, 1) ' x is row, 1 is col change if data in different col

a = Mid(Z, 1, 1) 'first character

b = Mid(Z, 2, 1) ' normally i would put in a loop this is easier to understand

c = Mid(Z, 3, 1)

If a = "" Then mx = x - 1: GoTo done 'out of data adjust mx value then exit

dn(x, 1) = a: dn(x, 2) = b: dn(x, 3) = c

Next x

done:

Return

compare: ' compare the two arrays

m = 0

For Z = 0 To 9

If tn(Z) = tar(Z) Then m = m + 1

Next Z

If m = 10 Then

Cells(mt, 3) = w ' box or str match in previous game # display in col C

For y = 1 To 3: Cells(mt, y + 4) = dn(w, y)

Next y

mt = mt + 1

End If

Return

'End Sub only want one