Is there any way to apply random values to cells within Excel?

[ddrap14]

Hi all,

I have a spreadsheet that simulates sports leagues using an external random number generator. The sheet looks like this, but is much larger in scope.

As a result of this size, I'm wanting to remove the external generator and build in the ability to generate values within Excel. However, using in-cell formulas wouldn't work because I'd have to change every single formula every time I wish to simulate another year, completely defeating the purpose of becoming more efficient.

Is this possible in Excel?

[#2]

@ddrap14 

I will assume you are familiar with the RAND function.  So you want formulas to derive a random number for each team, but retain that value in a cell until you explicitly reset it (without having to change the formula, of course).  So if this is the upper left part of your worksheet (i.e., team names in column A):

Premier League	1
Manchester City	77
Chelsea	73.8
Arsenal	72.6
Manchester United	89.5
Brighton and Hove Albion	72.7
Burnley	93.6
Wolverhampton Wanderers	60.6

The formula I put in cell B2 computes a (linear-distribution) random number from 60 to 100, rounded to the first decimal place, but only if the content of cell A1 is "RESET":

=IF( $A$1="RESET", ROUND(60+RAND()*40,1), B2 )

(Depending on your region settings, your syntax might require semicolons instead of commas as the list separators.)  Copy that formula down for each team in the list.  Then, change "Premier League" to "reset", (do a manual recalculation of formula values, if you don't have automatic recalculation on,) and then change it back.  And for the start of each year, make that pair of changes again to cell A1 to generate new random values.

You can of course choose some cell other than A1.  You may want to add a reminder note to A1/the cell that you choose, and/or more explicit documentation elsewhere in the workbook.

(This is one of the few instances when you want a formula that refers to the cell in which it is located.)

[#3]

This is just about what I was looking for; thank you so much.

I have just one follow-up question, and I'm fully aware that it's because I failed to adequately explain what I was looking for in my main post, which I apologise for. The way I've been doing it is that I've been adding smaller random values (-5.0 to +5.0) to each cell. How would I modify that formula so that it can do that, if it's at all possible?

[#4 · Accepted solution]

@ddrap14 

So at the start of a year, you want numbers that are a combination of user-entered values and random adjustments.  With that clarification, it seems that you should use one column for the values you enter, and a separate column for the "tweaked" values (i.e., the values after the random adjustments).  So putting the former into column B and the latter into column C, copy this formula from cell C2 down:

=IF( $A$1="TWEAK", B2 + ROUND(10*RAND()-5, 1), C2 )

and use the special word "TWEAK" instead of "RESET".

[#5]

Awesome, thank you so much! Apologies again for my failure to properly explain.