Formula for Pick 4 and Pick 5 workout.

[MrsFrankieH]

Hello,

I'm using Windows 7 and Excel 2010. Could you please help me (show me how) to write formulas for these workouts? My workout starts a bit further down the page, i hope that's ok.http://





Thank you very very very much!

Frankie

P.S.- This is my first "link" post.

 

[buxtongt]

Doing this one step at a time since there is a lot of it. For the odd/even stuff you could use:

=IF(EVEN(A1)=A1,A1,"")
=IF(ODD(A1)=A1,A1,"")

Hope this helps.

 

[MrsFrankieH]

Hello Buxtongt!!!

Thank you for helping me!! I'll start with this first. I appreciate your help!!

Frankie

 

[buxtongt]

For the repeaters, use:

=IF(OR(A1=A2,A1=B2,A1=C2,A1=D2),A1,"")

You can use something very similiar (except you compare it to the same row, not the row below) for the doubles, triples and quads.

 

[buxtongt]

For the sequentials you can use:

=IF(OR(B1+1=C1,B1-1=C1,B1+1=A1,B1-1=A1),B1,"") for the middle 2 columns, the 2 on the end will just have to be compared to the one next to them.

I think I need a little clarification on what you want for the leap frog columns.

 

[MrsFrankieH]

Thank you buxtongt!! :D

I just did the "odds" and "Evens" and they worked like a charm!! Thank you!!

Now i will try the Repeaters and then the sequentials.

I'll keep you updated with my progress.

Thank you again.

 

[MrsFrankieH]

Thank you buxtongt!! :D

I just did the "odds" and "Evens" and they worked like a charm!! Thank you!!

Now i will try the Repeaters and then the sequentials.

I'll keep you updated with my progress.

Thank you again.

 

[MrsFrankieH]

The 'Leap Frog' is a term used when a number increases or decreases in the next draw. For example; if the last draw was 2-0-1 and the current draw is 3-1-5


2 0 1
3 1 5

The number 3 decreases by 1 in the next draw (2)
The number 1 decreases by 0 in the next draw (0)



If possible could you color increases and decreases like shown? Note: "9" is greater than "0"

I'll keep you updated with my progress.

Thank you again. )

 

[buxtongt]

Something along the lines of:

=IF(OR(A2-1=A1,A2+1=A1),A2,"")

should work for the leap frog stuff, based on what I think you want it to show. For the colors you will need to use conditional formatting. Hope this helps.

 

[BJungheim]

All formulas go in the top left of their respective sections and can be copied across and down:
Vtracs:

=IF(R88="","",--RIGHT(R88+5,1))

Odd:

=IF(ISODD(R88),R88,"")

Even:

=IF(R88="","",IF(ISEVEN(R88),R88,""))

Repeaters:

=IF(R88="","",IF(OR(COUNTIF($R87:$U87,R88)>0,COUNTIF($R89:$U89,R88)>0),R88,""))

Leap Frog (couldn't figure out your formatting):

=IF(R88="","",IF(OR(ISNUMBER(MATCH(--RIGHT(9+R88,1),$R87:$U87,0)),ISNUMBER(MATCH(--RIGHT(11+R88,1),$R87:$U87,0)),ISNUMBER(MATCH(--RIGHT(9+R88,1),$R89:$U89,0)),ISNUMBER(MATCH(--RIGHT(11+R88,1),$R89:$U89,0))),R88,""))

Sequentials:

=IF(R88="","",IF(OR(ISNUMBER(MATCH(--RIGHT(9+R88,1),$R88:$U88,0)),ISNUMBER(MATCH(--RIGHT(11+R88,1),$R88:$U88,0))),R88,""))

Doubles:

=IF(COUNTIF($R88:$U88,R88)=2,R88,"")

Triples:

=IF(COUNTIF($R88:$U88,R88)=3,R88,"")

Quadruples:

=IF(COUNTIF($R88:$U88,R88)=4,R88,"")