Forum Discussion
Is there any way to apply random values to cells within Excel?
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".
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.)
- 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?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".
- Awesome, thank you so much! Apologies again for my failure to properly explain.
