Help with my Lotto code please

[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"))))))

 

[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

 

[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

 

[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.

 

[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

 

[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.

 

[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

 

[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"}))

 

[p45cal]

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:

 

[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.

 

[Harry]

Thanks for the help p45cal,

I don't know how it works but it does.

Regards,

Harry.

 

[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.

 

[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.

 

[Harry]

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

Harry.

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