Help with my Lotto code please

H

Harry

Would some kind person please make this formula work for me, it’s for a
lottery numbers checker I’m making, all works great but for this last bit.
I’ve tried many different ways without luck. I’ve left it this way as it best
shows what I want it to do. I’m sure it is probably an easy formula for those
who do these things but I’m just playing with it and learning. I’m using
Excel 2007.
We do 20 tickets 2 times a week always with the same numbers so the checker
just needs the winning numbers entered to give the results.
Thank you in advance for any help.
Harry.
Cell S9 is the number of winning numbers between 1-6 and cell W9 is the
bonus ball 0 or 1.
=IF(S9<3,"NO GOOD",IF(S9=3,"£10",IF(S9=4,"ABOUT £50",IF(S9=5,"ABOUT
£500",IF(S9+W9=6,"BIG MONEY",IF(S9=6,"JACKPOT"))))))
 
M

Mike H

Harry,

Try this

=IF(S9=3,"£10",IF(S9=4,"About £50",IF(S9+W9=6,"Big Money",IF(S9=5,"About
£500",IF(S9=6,"Jackpot","No Good")))))

Mike
 
P

p45cal

if

Code
-------------------
=CHOOSE(S9,"no good","no good","about £10","about £50",CHOOSE(W9+1,"about £500","big money"),"jackpot"
-------------------
doesn't give you what you want, clarify the problem by filling in belo
what you want to see in each circumstance:

Code
-------------------
Winning nos. | Bonus Ball | RESULT
1 0 no good
1 1 no good
2 0 about 10
2 1 ?
3 0 ?
3 1 ?
4 0 ?
4 1 ?
5 0 ?
5 1 ?
6 0 jackpot
6 1
 
H

Harry

Thanks for getting back to me Mike, I tried your formula but it does the same
as mine did. It doesn’t show Jackpot with 6 Balls. Any ideas?
Thanks
Harry.

1 Ball = No Good S9
2 Balls = No Good S9
3 Balls = £10 S9
4 Balls = About £50 S9
5 Balls = About £500 S9
5 Balls and Bonus Ball = Big Money S9 + W9
6 Balls = Jackpot S9
It’s the bonus ball that causes the problem as it involves two 5 Balls and a
total of 6 twice.
 
H

Harry

Thanks p45cal,

Hope below explains things a bit better. Appreciate your help. I don’t do
much of this but I’m finding it fun.

Thanks,
Harry.
Code:
--------------------
Winning nos. | Bonus Ball | RESULT
1 0 no good
1 1 no good
2 0 no good
2 1 no good
3 0 £10
3 1 £10
4 0 About £50
4 1 About £50
5 0 About £500
5 1 Big money
6 0 jackpot
 
H

Harry

Hi again Mike,
Tried your code again, it almost works, I found it has to have 6 balls and
the Bonus ball to return “Jackpotâ€. The lottery here in the UK only has 6
numbers so 6 balls and the Bonus ball never occurs. Only five balls brings
the bonus ball into play.
Harry.
 
M

Mike H

Harry,

I see my error, try this

=IF(S9=3,"£10",IF(S9=4,"About £50",IF(AND(W9>0,S9+W9=6),"Big
Money",IF(S9=5,"About £500",IF(S9=6,"Jackpot","No Good")))))

Mike
 
B

barry houdini

Try either

=IF(S9=6,"JACKPOT",IF(S9=5,IF(W9=1,"BIG MONEY","ABOUT
£500"),IF(S9=4,"ABOUT $50",IF(S9=3,"£10","NO GOOD"))))

or

=IF((S9=5)*(W9=1),"BIG MONEY",LOOKUP(S9,{0,3,4,5,6;"NO
GOOD","£10","ABOUT £50","ABOUT £500","JACKPOT"}))
 
P

p45cal

Harry;469159 said:
Thanks p45cal,

Hope below explains things a bit better. Appreciate your help. I
don’t do
much of this but I’m finding it fun.

Thanks,
Harry.
Code:
--------------------
Winning nos. | Bonus Ball | RESULT
1 0 no good
1 1 no good
2 0 no good
2 1 no good
3 0 £10
3 1 £10
4 0 About £50
4 1 About £50
5 0 About £500
5 1 Big money
6 0 jackpot

which is the same result as my suggestion earlier:
if
Code:

=CHOOSE(S9,"no good","no good","about £10","about
£50",CHOOSE(W9+1,"about £500","big money"),"jackpot")

doesn't give you what you want, clarify the problem by filling in below
what you want to see in each circumstance:
 
H

Harry

Hi Mike,
That works great. A bit more complicated than I thought and beyond my
capabilities.
I don’t suppose “Jackpot†will ever pop up, who knows?
Thanks very much for your help.
Regards,
Harry.
 
H

Harry

Hello Barry,

Well they both work, I now have four different ways to do the same thing, no
wonder I can't get my head around it all.

Thanks very much for the help.

Regards,

Harry.
 
S

Shane Devenshire

Hi,

Suppose you set up the following lookup table:
0 No Luck
3 £10.00
4 About £50.00
5 About £500.00
5.5 Big Money
6 Jackpot

Then you could use a simple formula like:

=VLOOKUP(W9/2+S9,A1:B6,2)

where the table is in the range A1:B6.
 
H

Harry

Thank's Shane, I'll give it a try.

Harry.

Shane Devenshire said:
Hi,

Suppose you set up the following lookup table:
0 No Luck
3 £10.00
4 About £50.00
5 About £500.00
5.5 Big Money
6 Jackpot

Then you could use a simple formula like:

=VLOOKUP(W9/2+S9,A1:B6,2)

where the table is in the range A1:B6.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top