Excel: Dynamic cell reference for Roulette

Question

I try to build a roulette (0-36) and have a question regarding cell reference: I would like to define what happens when someone is betting on Red and that excel will apply the definition of red only when needed. (More general: I would like to define all roulette betting options such as Odd, Column bet, Corner etc. and apply the definition when needed.)

Look at the image: Two people are betting on Red, but only one result is correct: D2 (as a correct Red bet will double). D3 is not doubled and, therefore, it is wrong.

Formulas:

D2: =IF(B2="Red";C5;IF(B2="Black";C6;C2)) In this formula I would like to reference the underlying formula C5 what is working. (In this scenario only the part B3="Red";C5is relevant.)

D3: =IF(B3="Red";C5;IF(B3="Black";C6;C3)) In this formula I would like to reference the underlying formula C5 what does NOT work. (In this scenario only the part B3="Red";C5is relevant.)

C5: =IF(OR($A$2=1;$A$2=3;$A$2=5;$A$2=7;$A$2=9;$A$2=12;$A$2=14;$A$2=16;$A$2=18;$A$2=19;$A$2=21;$A$2=23;$A$2=25;$A$2=27;$A$2=30;$A$2=32;$A$2=34;$A$2=36);C2*2;-C2)

The (supposed) problem: That the C5 formula is referenced to C2 (see last two elements).

My goal: Whenever there is a "Red" entry in the Bet section (e.g. column B) it will apply the (corrected) Definition Red C5. In order to do that the part C2*2;-C2of the C5 formula must change according to the cell where the "Red" bet is: Whether B2 or B10 is "Red" it should apply the underlying formula. And, it should apply the correct formula for multiple bets like in my example.

I hope you understand what I try to say. Would be happy if anyone can help me:-)

Answer 1

If I'm understanding everything properly, it might not be possible to use a single cell for the winning amount C5 and the losing amount C6. The reason is, there will be a different winning amount and losing amount each time based on the bet. That's why Row 2 is working. It uses the Row 2 stake C2 in the winning amount formula C5. You could get Row 3 to work by using C3*2; -C3 in the winning amount formula C5, but then D2 would be broken instead!

Here are two screen shots using Excel 2010 that might help (this was also tested in LibreOffice 5.1.6.2). This is a partial solution, though it is admittedly not the best. However, the idea should work using built-in functions (no VBA).

Example 1

Example 2

Remarks

This solution requires that an exhaustive list of bet types be constructed (Column I) along with the payout multiplier if the bettor's bet type ends up being a winner (Column K). Column J contains the calculations for whether or not the resulting number A2 results in a win (TRUE) or loss (FALSE) for the Bet Type. The calculations I used for the examples shown are:

• Red: =OR($A$2=1,$A$2=3,$A$2=5,$A$2=7,$A$2=9,$A$2=12,$A$2=14,$A$2=16,$A$2=18,$A$2=19,$A$2=21,$A$2=23,$A$2=25,$A$2=27,$A$2=30,$A$2=32,$A$2=34,$A$2=36)
  • Perhaps a cleaner alternative: =OR($A$2={1,3,5,7,9,12,14,16,18,19,21,23,25,27,30,32,34,36})
• Black: =OR($A$2=2,$A$2=4,$A$2=6,$A$2=8,$A$2=10,$A$2=11,$A$2=13,$A$2=15,$A$2=17,$A$2=20,$A$2=22,$A$2=24,$A$2=26,$A$2=28,$A$2=29,$A$2=31,$A$2=33,$A$2=35)
  • Perhaps a cleaner alternative: =OR($A$2={2,4,6,8,10,11,13,15,17,20,22,24,26,28,29,31,33,35})
• Low: =AND($A$2>=1,$A$2<=18)
• High: =AND($A$2>=19,$A$2<=36)
• Odd: =MOD($A$2,2)=1
• Even: =MOD($A$2,2)=0
• 1st Dozen: =AND($A$2>=1,$A$2<=12)
• 2nd Dozen: =AND($A$2>=13,$A$2<=24)
• 3rd Dozen: =AND($A$2>=25,$A$2<=36)

I'm no roulette expert so I might not have them quite right... But I'm guessing you know the winning formulas already.

The problem with my approach is that creating the exhaustive list of wins (Columns I, J, and K) will take a lot of effort. There might be a more elegant solution, but I'd have to think more about it.

---

Now you can take each bettor's bet type (Column B) and find its corresponding index from the set of all bet types (I2:I10) using the MATCH function. The results of this process are shown in Column C. Column D is just an illustration of the formula, so you don't need to include it in your spreadsheet.

Once you have the index, you can use it along with the INDEX function to look in J2:J10 to see if the bet type is a winner or not. If it's a winner, you can also use the index to find the appropriate Payout Multiplier from K2:K10 and multiply it by the stake in Column E. If it's not a winner, the result is the loss of the stake. The results of this process are shown in Column F. Column G is just an illustration of the formula, so you don't need to include it in your spreadsheet.

Keep in mind that the Column I, J, and K ranges will need to expand as you add more bet types. So instead of I2:I10, J2:J10, and K2:K10, you'll end up with larger ranges.

I explained the MATCH and INDEX functions a little more thoroughly in this answer, though you'd probably be even better served just looking them up in an online manual somewhere.