uk lotto dream numbers

[Guest]

hello again, can some one hlep with this formula,

=IF(AND($B$4=B7,$C$4=C7,$D$4=D7,$E$4=E7,$F$4=F7,$G$4=G7),"!! JACKPOT
!!",IF(AND($B$4=B7,$C$4=C7,$D$4=D7,$E$4=E7,$F$4=F7),"1st 5
Balls",IF(AND($B$4=B7,$C$4=C7,$D$4=D7,$E$4=E7),"1st 4
Balls",IF(AND($B$4=B7,$C$4=C7,$D$4=D7),"1st 3
Balls",if(and($b$4=b7,$c$4=c7,"1st 2 Balls",""))))

its to check looto cells $b$4 to $g$4 agains my ticket b7 to g7
dont know if everyone knows how the lotto dream numbers works
but you must match a minimum of 1st 2 numbers to win
so answers can only be 1st 2 balls, 1st 3 balls , 1st 4 balls, 1st 5 balls ,
jackpot

theres probably an easyer way to do the samething but i aint got a clue on
it...

 

[Don Guillett]

where row 7 is your numbers and row 5 is the winning numbers will give the
number of matches for for 7

=IF(G7=0,"",SUMPRODUCT(COUNTIF(B7:F7,$B$5:$F$5)))

 

[Guest]

sorry don not what i'm after. yur formula returns the correct number of
balls, but in order to win you must have the balls in correct order.
b5 to g5 = 10,28,30,34,36,40
b7 to g7 = 8,27,30,34,36,40
your formula returns 4 in this case
i need it to match the 1st 2 numbers to give a result

so if b7 to g7 = 10,28,30,35,38,40 would = 3 numbers
where as b7 to g7 = 9,28,30,35,38,40 would = nothing
also b7 to g7 = 10,27,30,34,36,40 would = nothing

hope this helps , dunno if i've made it more complicated now

but i can still use your formula to tidy up anther thing i've got going so
many thanks for that

 

[Don Guillett]

try this idea for 3 matches

=IF((B5&C5&D5)=($B$4&$C$4&$D$4),3,if(etc)

 

[Guest]

Thats the one .. Cheers Don works a treat

try this idea for 3 matches

=IF((B5&C5&D5)=($B$4&$C$4&$D$4),3,if(etc)

 

[Don Guillett]

glad to help. I quit playing the Texas Lotto a couple of years ago but I had
a rather involved workbook to keep track. I do custom work.

 

[Guest]

I'm guessing the one person that can afford to play lotto (Mr. Gates)
doesn't! Why do you suppose that is???

 

[Don Guillett]

Gee, and I always thought that was how he got his start!

 

[Ragdyer]

Heck ... I thought he got his start from robbin ... er, I mean using the GUI
from Xerox!<bg>