Welcome Guest
You last visited December 8, 2016, 4:55 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel Help!!

Topic closed. 11 replies. Last post 11 years ago by CARBOB.

 Page 1 of 1
Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: September 16, 2005, 5:09 pm - IP Logged

Calling all Excel Guru's!!!

Looking for a formula for tracking last 3 skips from a Pick 3 drawing history.

For example:

Say I have a drawing history and I whant to know when the digit #3 hit the last 3 times in position 1.

So my chart would look something like this:

Pick 3

Position 1    Hit#1    Hit#2    Hit#3

0                (these would be the drawing number(s) the hits occured

1

2

3

4

5

6

7

8

9

Thanks in advance for the help!!!

wsls

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: September 16, 2005, 7:19 pm - IP Logged

I would like that formula as well if there is such a formula.

For the time being, I simply use a conditional format in order to find what I am looking for in whatever position (or all 3) that I need it for.

Dump Water Florida
United States
Member #380
June 5, 2002
3104 Posts
Offline
 Posted: September 17, 2005, 3:47 am - IP Logged

What's Excel mean when you get an error "not a valid interger"  after typing in your numbers?

Tx
United States
Member #4570
May 4, 2004
5180 Posts
Offline
 Posted: September 17, 2005, 10:08 am - IP Logged

While I don't know anything about Excel myself maybe you can try asking here:

Good luck.

"Ten measures of beauty descended to the world, nine were taken by Jerusalem."

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: September 18, 2005, 2:34 pm - IP Logged

I need a formula that will allow me to track 1, 2, or 3 digit returns.  Does anyone have such a formula that they are willing to share?

East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: September 19, 2005, 3:04 pm - IP Logged

What's Excel mean when you get an error "not a valid interger"  after typing in your numbers?

This is known to occur when one either types in a non-numeric character (a-z, and any of the other symbols). It can also occur when you hit the period by mistake. Because the program is attempting to make a literal translation, a non-whole number (n.0001 thru n.9999, n=any number), it sees a variable ( I forget the exact syntax). Remember, an interger is a whole number with no decimals.

Hope this helps.

BTW, a workaround I use to avoid that error is to multiply that cell value with a 1 to insure it converts the data to a number value. Of course, this only works in formulas. If you are programming in VBA, you will have to do a hard conversion.

Hope this helps,

Sir Metro

East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: September 19, 2005, 3:10 pm - IP Logged

I need a formula that will allow me to track 1, 2, or 3 digit returns.  Does anyone have such a formula that they are willing to share?

Consider this as an idea (one that can be expanded upon and compounded if you pay attention the to parenthesis's that will be required)

if the data in Cell A1 = 200-90 = 110, then

=((LEN(TEXT(A1,"###")))) <---converts the number to a string and then looks to see how many spaces that string takes up. The ### is telling the computer that the data is in number format

the above will give you 3 digit long.

Perhaps this is what you are looking for?

Sir Metro

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: September 19, 2005, 3:40 pm - IP Logged

Thanks SirMetro

I will give it a try & let you know if it's what I'm looking for & how it works.

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5901 Posts
Online
 Posted: September 19, 2005, 4:19 pm - IP Logged

This formula tracks skips.

=IF(ISNUMBER(MATCH(W17,\$K\$7:\$K\$3919,0)),\$A\$7-(INDEX(\$A\$7:\$A\$2051,MATCH(W17,\$K\$7:\$K\$3919,0))),"")

this formula tracks number of digits that carried. It's rather long.

=MIN(COUNTIF(G10:I10,G10),COUNTIF(G11:I11,G10))/COUNTIF(G10:I10,G10)+MIN(COUNTIF(G10:I10,H10),COUNTIF(G11:I11,H10))/COUNTIF(G10:I10,H10)+MIN(COUNTIF(G10:I10,I10),COUNTIF(G11:I11,I10))/COUNTIF(G10:I10,I10)

Carbob

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: September 19, 2005, 4:25 pm - IP Logged

Thanks Carbob--I like Long   (formulas that is)

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: September 19, 2005, 4:33 pm - IP Logged

This formula tracks skips.

=IF(ISNUMBER(MATCH(W17,\$K\$7:\$K\$3919,0)),\$A\$7-(INDEX(\$A\$7:\$A\$2051,MATCH(W17,\$K\$7:\$K\$3919,0))),"")

this formula tracks number of digits that carried. It's rather long.

=MIN(COUNTIF(G10:I10,G10),COUNTIF(G11:I11,G10))/COUNTIF(G10:I10,G10)+MIN(COUNTIF(G10:I10,H10),COUNTIF(G11:I11,H10))/COUNTIF(G10:I10,H10)+MIN(COUNTIF(G10:I10,I10),COUNTIF(G11:I11,I10))/COUNTIF(G10:I10,I10)

Carbob

OK.  I am trying to think this one thru.

I take it that the number that I am searching for would be in W17.

K7 thru K3919 would be the ranges of my search & the 0 would be an exact match

so would A7 be the date?

Am I correct in my thinking?

I have to do this process so that I can change the formula to fit my spreadsheet (as I don't know where your draws start in yours)

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5901 Posts
Online
 Posted: September 19, 2005, 6:48 pm - IP Logged

This formula tracks skips.

=IF(ISNUMBER(MATCH(W17,\$K\$7:\$K\$3919,0)),\$A\$7-(INDEX(\$A\$7:\$A\$2051,MATCH(W17,\$K\$7:\$K\$3919,0))),"")

this formula tracks number of digits that carried. It's rather long.

=MIN(COUNTIF(G10:I10,G10),COUNTIF(G11:I11,G10))/COUNTIF(G10:I10,G10)+MIN(COUNTIF(G10:I10,H10),COUNTIF(G11:I11,H10))/COUNTIF(G10:I10,H10)+MIN(COUNTIF(G10:I10,I10),COUNTIF(G11:I11,I10))/COUNTIF(G10:I10,I10)

Carbob

OK.  I am trying to think this one thru.

I take it that the number that I am searching for would be in W17.

K7 thru K3919 would be the ranges of my search & the 0 would be an exact match

so would A7 be the date?

Am I correct in my thinking?

I have to do this process so that I can change the formula to fit my spreadsheet (as I don't know where your draws start in yours)

W17 (0)X17(1),Y17(2),Z17(3) is whatever you want to track, Ex 0,1,2,3 numbers carried.  K is col where I have the second formula stored which tells you how many digits carried. A is the date col.

You can PM with any questions.

Carbob

 Page 1 of 1