I want to simulate playing mega millions. The megaball number is simple and I don't need help with it. I want 5 random numbers generated from 1 through 70. No duplicates in same row. I want matching numbers highlighted. I try to use the COUNTIF function but it will only highlight if the first cell in the block matches one of the numbers in the list of winning numbers and it will highlight the enter block instead of individual matching cells. I suspect it is because it is in conflict with the RANDBETWEEN function. Also, I don't know how to have it not create duplicate numbers in same row. Plz halp.

you probably need to use some other function nested with it.

My work around is to create a rule for each number matching the mega millions winning number. I don't know how to make it work as one rule. GD easier to code it in java but I don't want to create a GUI just for this. Fk it I just might if nobody can teach me in excel.

I use a simple macro for this, but it's probably simplest/easiest just to do it like how excel tells you:

1. In a blank worksheet, type column headers in Range A1:C1 as below screen shot shown:


2. Enter formulas in Column A, Column B, and Column C:


(1) In Cell A2, enter the formula =RAND(), and then drag the Fill handle to the range A2:A50.
(2) In Cell B2, enter the formula =RANK($A2,$A$2:$A$50)+COUNTIF(B$1:B1,B1)-1, and then drag the Fill Handle to the Range B2:B50. (In the formula, A2 is the random number left to current cell, $A$2:$A$50 are all random numbers we got with the first formula, B$1 is the first cell in Column B, and B1 is the cell above current cell).
(3) In Range C2:C50, enter sequence numbers from 1 to 49.

3. Find a blank range, type sequence numbers horizontally in a row as below screen shot shown:


4. Below the first sequence number of 1, enter the formula =VLOOKUP(G1,$B$1:$C$50,2,FALSE) into it, and the drag the Fill Handle to the Range you need.


5. It's optional. To prevent the lottery numbers from changing automatically when current worksheet refreshes, you can click the Formula > Calculation Options > Manual.

[QUOTE=ErnieMccracken post_id=1674218333]I use a simple macro for this, but it's probably simplest/easiest just to do it like how excel tells you:

1. In a blank worksheet, type column headers in Range A1:C1 as below screen shot shown:
[img]https://i.imgur.com/tcJJAd1.png[img]

2. Enter formulas in Column A, Column B, and Column C:
[img]https://i.imgur.com/XWYHbqG.png[img]

(1) In Cell A2, enter the formula =RAND(), and then drag the Fill handle to the range A2:A50.
(2) In Cell B2, enter the formula =RANK($A2,$A$2:$A$50)+COUNTIF(B$1:B1,B1)-1, and then drag the Fill Handle to the Range B2:B50. (In the formula, A2 is the random number left to current cell, $A$2:$A$50 are all random numbers we got with the first formula, B$1 is the first cell in Column B, and B1 is the cell above current cell).
(3) In Range C2:C50, enter sequence numbers from 1 to 49.

3. Find a blank range, type sequence numbers horizontally in a row as below screen shot shown:
[img]https://i.imgur.com/EvyleE6.png[img]

4. Below the first sequence number of 1, enter the formula =VLOOKUP(G1,$B$1:$C$50,2,FALSE) into it, and the drag the Fill Handle to the Range you need.
[img]https://i.imgur.com/Q44MTyi.png[img]

5. It's optional. To prevent the lottery numbers from changing automatically when current worksheet refreshes, you can click the Formula > Calculation Options > Manual.
[img]https://i.imgur.com/3CXGGWY.png[img][/QUOTE]
This is what I'm working with:

Sometimes there will be duplicates within a row (each row equaling one ticket). How do I not have that happen?

[img]https://i.imgur.com/dwgFIXx.png[/img]

The way you have it set up, you cannot do it without a macro function AFAIK. At at least I don't know how.

To do it with regular vanilla formulas, that's what the RAND and RANK formulas are for.

Cliffs:
1. Start with 69 rows of data, each with a RAND formula. You draw a bunch of RANDs, which are brought out to 9 decimals in this case to make sure there are no dupes..
2. In the next column, you take the RAND column and RANK them 1-69, so each drawn number now has a unique integer rank. Now you will have rank (column b) to go with your ball number (column c).
3. You pull in "winners" using the VLOOKUP formula.

Do a Monte Carlo simulation using Python for this.

brb learning python to lottery sim

Also you can do monte carlo sim in excel ez pz, but only really useful if you're tabulating many results.

Paging all financecels

You should have seen our fantasy football spreadsheets. We bloatmaxxed it up to almost 200mb, looked like a terminal.

Just use VBA for whatever you want to say.