Randomization Based Off a Set of Rules

[Kawan]

Hi everyone,

I have a rather tricky problem to solve.

For each row in my table (for each person) - refer to screenshot named "My Table", I would like excel to randomly generate a 9 digit number (in Column N) but based on a certain set of rules.

Here is how I currently generate them 1 by 1 manually:

1. Yellow cells: I only modify the numbers of this row.

• The first 3 digits of this row are based off the person's year of birth (DOB Column in the 'Results' worksheet). A 3 digit code is assigned based on the year of birth (refer to screenshot called "First 3 digits").
• Then I manually change the last 6 digits of this row one by one so that the green cell equals to an even number ending in 0, so: 10 or 20 or 30 or 40 or 50 or 60, etc.

2. Orange cells:

• This row is the factor row and never changes, each of the number above it is multiplied by this factor. So in the example above, 3 (in yellow) will be multiplied 1 (in orange), 9 (in yellow) by 2 (in orange), 4 (in yellow) by 1 (in orange), and so fourth.

3. Red cells: The formula for each red cell is the same with the exception of the reference cells in the formula, so for example for cell F5 the formula is:

=IF(SUM(F3*F4)>=10,SUM(F3*F4)-9,SUM(F3*F4))

for cell G5 the formula is:

=IF(SUM(G3*G4)>=10,SUM(G3*G4)-9,SUM(G3*G4))

4. Green Cell: This cell is just the SUM of of the red cells, so

=SUM(F5:N5)

It must always equal to an even number ending in 0, so: 10 or 20 or 30 or 40 or 50 or 60, etc...


To sum it up:

For each row in my table, so for each person (refer to screenshot called "My table" in the results worksheet), I would like excel to generate a random 9 digit number in the column called "9 Digit Number" (Column N) based on the set of rules explained above.

I have thousands of rows so you can understand why I would like this process to be automated.

Please do not hesitate to ask me should you require more clarification/information.

Thank you,
Kawan.