Can't search Last Time String event happened

[david gonzalez]

Hi,

This is for pick 4 Lotto, there are 4 Position and on each pick position play the Numbers 0 to 9.

please need some help.

Sample File: https://dl.dropboxusercontent.com/u/...mbersSkip.xlsx

This is the problem that i got :
I need to Know how many days (drawn) each number 0 ~ 9 , from each 4 position have skipped
(Pos 1, Pos 2, Pos 3, Pos 4))

example :

PICK4 Position 1
Numbers ------> 1 2 3 4 5 6 7 8 9 0
Days Skipped--> ? ? ? ? ? ? ? ? ? ?


Note :
i have a formula and is working good, when 4 numbers win, this formula
show many days (each of the 4 winners) skipped before win,
For Pick Position 1->

Please Login or Register  to view this content.

For Pick Position 2->

Please Login or Register  to view this content.

For Pick Position 3->

Please Login or Register  to view this content.

For Pick Position 4->

Please Login or Register  to view this content.

But i can't apply the same formula to the all 9 numbers to get result correctly,I applied the formula on diferent ways
and i got result of "0", or #VALUE error

PICK4 Position 1
Numbers ------> 1 2 3 4 5 6 7 8 9 0
Days Skipped--> ? ? ? ? ? ? ? ? ? ?

P.S
Some more notes (explanation) in sample file, and also more Msg with Formulas that i used, but didn't work (on this same post)


Thank you very much

Best Regards

David

[david gonzalez]

I Found a array Formula to solve my problem, i applied in G:3 under Number 1, Position 1 table
and i got a Value 41596, and Is Suppose to have a Value 7

This is the array formula :

Please Login or Register  to view this content.

and it doesn't work, Please can some one tell me where is the mistake?

Notes:
1-. copied Array Formula, Using Shift+Ctrl+ Enter
2-. My data cell's or any other Cell is NOT formatting as "Text", Except the Date Box, is formatting a a Date :-)
3-. I recreate the Data and formula in brand new Sheet

And still the same problem.

Thank you very much


Best Regards

David

[daddylonglegs]

G2 is a number and you are comparing it against text values so there is no match and MAX returns 0, to fix that try this version

=TODAY()-MAX(IF(G$37:G$166=$G2&"",$B$37:$B$166))

confirmed with CTRL+SHIFT+ENTER

when I use that in G3 and format as general I get 18 (today minus 1st November), how do you get 7?

[david gonzalez]

Hi Mr daddylonglegs,

I'm sorry, my mistake on Previous post

I Found a array Formula to solve my problem, i applied in G:3 under Number 1, Position 1 table
and i got a Value 41596, and Is Suppose to have a Value 7

I was referring to Position 1, Number 7, which if was drawn on Nov 3, and had an skip of 7 days.
G:37 Pick 1 -> #7 AB:37 Have Skip 7 (of Pick 1)

But anyway, (using different cells) some guy had a question like mine in 2010, (the same formula that i use today), was explained on a site MR Something excel. and on pics "seems" that work,. but not with me.

i don't wants to violate any rues, please let me know if i'm allows to post links.
[link]http://www.mrexcel.com/forum/excel-questions/479070-help-pick-3-lottery-formula.html [/link]

P.S
in Row AB37:AE37 I'm using the array

Please Login or Register  to view this content.

and as you see, is counting correct the skip of Rows G37:J37.
But it doesn't reading the skips when applied in row3 (just last 4 digits drawn)


Thank Sir.


Best Regards

David

[david gonzalez]

Hi forum members!!

I'm wonder, if a forum member check what i'm doing wrong

I goggled and found a Tread with a answer that is exactly what i need, i applied their formula that they say to my sheet
and still won't work
This is my sample file : https://dl.dropboxusercontent.com/u/...mbersSkip.xlsx

This is the tread that someone posted some years ago

Skips for Pick 3 draws

Lets say your data in Excel has the draw number in column A and N1, N2 and N3 in columns B, C and D starting on row #3 and going down. Row 2 can be used for labels. Note it might be best if the width of columns is small in order to see more (a width of 5 is enough).
A - Skips not considering the position
======================================
The method listed here does take into consideration the position a number has come out but simply when it did come out last.

In cells F1 to O1, enter the consecutive numbers, 0,1, 2, &, 9. In cell F3, enter the formula =IF(COUNTIF($B3:$D3,F$1)>=1,0,F2+1). Then copy this formula across to column O, and down for all the draws.

This will give you a 0 if the number in the top row has come out in the draw or otherwise a number indicating how many draws the number has not come out.

B Skips considering the position
================================
This method now takes into consideration the position of the number, either in first, second or third position.

-In cells F1 to O1, enter the consecutive numbers, 0,1, 2, &, 9 for numbers in the first position,
then repeat this,
-In cells Q1 to Z1 for numbers in the second position, and
-In cells AB1 to AK1 for numbers in the third position.
.In cell F3, enter the formula =IF($B3=F$1,0,F2+1),
.in cell Q3, enter the formula =IF($C3=Q$1,0,Q2+1) and
.in cell AB3,enter the formula =IF($D3=AB$1,0,AB2+1).
Then copy each formula across each set of data and down for all draws.

This will give you a 0 if the number in the top row has come out in the draw or otherwise a number indicating how many draws the number has not come out in a specific position depending on which block of data you are looking at.

Then I Applied (i added another Pick) the same formulas to my cells sheet as follow :

B Skips considering the position
================================
In row 2, enter Digits 0 ~9 on consecutive Cells :
---In cells G2 to P2, (first position)
---In cells G7 to P7 for numbers in the (second position)
---In cells G12 to P12 for numbers in the (third position)
---In cells G18 to P18 for numbers in the (fourth position)
.In cell G3, enter the formula =IF($G37=G$2,0,G3+1)
.in cell G8, enter the formula =IF($H37=G$7,0,H8+1)
.in cell G13,enter the formula =IF($I37=G$12,0,G13+1)
.in cell G19,enter the formula =IF($J37=G$18,0,G19+1)
Then copy each formula across each set of data and down for all draws.

So far no luck..

Can someone point where is my mistake??

Thank you!

Best Regards from miami

David

[david gonzalez]

Hi Mr daddylongleg,

I try the formula version, and didn't work.

On Row/Column -> G:2 (belong to Digit Zero "0" of PICK 1 Column) I pasted the formula that you
suggest me

Please Login or Register  to view this content.

confirmed wit Ctrl+Shift+Enter

and as result i got 39 Skip (the right skip have to be 23) because the zero is located on G:60



Thank you Sir.


Best Regards

David

[david gonzalez]

Hi, Good morning every one !!!!


Problem solved.

my mistake was on G2:P2 (numbers 1-9) each Cells where set as Text Cell and need an Apostrophe.
(but it's weird, because i recreate the sample File on a brand new sheet, with out setting text or number on any cell)

And i use the Formula :

Please Login or Register  to view this content.

I don't know the reason, but the formula that Mr DaddylongLeg that nicely suggest

Please Login or Register  to view this content.

Didn't work for me, it give me erratic values on the result, and i'm trying to figure it out
(is the way to learn)

Than k you very Much


Best Regards

David