asked on # Excel randomizer question

i have a spreadsheet with a data set that i want to interpret graphical with a bar chart. The problem i have is that i only have totals and i want the graph to illustrate variations. I have tried to make clear on the data file i included what kind of formula i am looking for but to restate...the blank cells in the worksheet need values and i want control how those values are generated. I also would like the graph to not look so extreme since one set of values is much higher than the other data sets.

thanks

thanks

Microsoft Excel

no data file

excel data file attached

Book2.xlsx

pdvsa,

This question would be easier go get a handle on if your workbook showed the type of numbers you want to be randomly generated.

Assuming that you want the numbers to approximate a random movement between the two extremes in C7 and C13, you might use a formula in cell C8 like:

=C$7+(C$13-C$7)*RAND()*ROWS(C$8:C8)/3 'can be copied down

The RAND() gives a random number between 0 and 1--with an average value of 0.5

As you copy ROWS(C$8:C8) down, it returns the numbers 1 through 5.

If you would have copied ROWS(C$8:C8) down through C13, the last value would have been 6. This is convenient, because you would have six random numbers whose average values are 1/6, 2/6, 3/6, 4/6, 5/6 and 6/6. Had RAND() returned exactly 0.5 in row 13, then the formula would have returned C$7+(C$13-C$7)*0.5*6/3 = C$13.

Brad

This question would be easier go get a handle on if your workbook showed the type of numbers you want to be randomly generated.

Assuming that you want the numbers to approximate a random movement between the two extremes in C7 and C13, you might use a formula in cell C8 like:

=C$7+(C$13-C$7)*RAND()*ROW

The RAND() gives a random number between 0 and 1--with an average value of 0.5

As you copy ROWS(C$8:C8) down, it returns the numbers 1 through 5.

If you would have copied ROWS(C$8:C8) down through C13, the last value would have been 6. This is convenient, because you would have six random numbers whose average values are 1/6, 2/6, 3/6, 4/6, 5/6 and 6/6. Had RAND() returned exactly 0.5 in row 13, then the formula would have returned C$7+(C$13-C$7)*0.5*6/3 = C$13.

Brad

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Thanks Brad. The workbook example i provided has the numbers in currency format and the values i am looking for should be in descending/ascending order from row 7 to 13 and vice versa.

This is a sample data set of a larger worksheet and i am looking for a formula to copy/paste. I used the formula you provided but the values it generated where identical for every cell. I tried a few variations but kept getting the same identical values.

This variation:

=C$7+(C$13-C10)*RAND()*ROWS(C$8:C$12)/5

on the formula would seem to provide the desired result but it generates an identical value...$13,879,069.83. Is there something i am missing in the syntax.

This is a sample data set of a larger worksheet and i am looking for a formula to copy/paste. I used the formula you provided but the values it generated where identical for every cell. I tried a few variations but kept getting the same identical values.

This variation:

=C$7+(C$13-C10)*RAND()*ROW

on the formula would seem to provide the desired result but it generates an identical value...$13,879,069.83. Is there something i am missing in the syntax.

Brad,

i think i see the syntax...i will work with this syntax. thanks

i think i see the syntax...i will work with this syntax. thanks

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

Another type of random number formula would permit random progress from the value in C7 to that in C13, *but with no backtracking.* Try the following in cell C8:

=C7+(C$13-C7)*RAND()*2/ROWS(C8:C$13) formula may be copied down

The above formula would be typical of sales volume--always increasing, but with variation in the month to month amounts.

=C7+(C$13-C7)*RAND()*2/ROW

The above formula would be typical of sales volume--always increasing, but with variation in the month to month amounts.

i attached a sheet with the second formula, gave no effect, graphs are inconsistent when i copied values. Is something missing in my how i copy the data values?

Book2Q27731257.xlsx

pdvsa,

The title of the question suggests that random numbers are required in a steadily changing range between the values in C7 and C13. I therefore suggested a formula using the RAND function. RAND will change value every time you enter data, no matter whether by typing it in or by copy & paste. That's why your two charts look different--it's by design.

If it is essential that the copy look just like the original data, change the calculation mode to manual before the copy & paste.

Brad

The title of the question suggests that random numbers are required in a steadily changing range between the values in C7 and C13. I therefore suggested a formula using the RAND function. RAND will change value every time you enter data, no matter whether by typing it in or by copy & paste. That's why your two charts look different--it's by design.

If it is essential that the copy look just like the original data, change the calculation mode to manual before the copy & paste.

Brad

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Brad,

For columns F & G the randomness needs to sum to the total of row 13 and those sums need to loosely reflect the decreasing value of column C. I had never altered the calculation mode before so i will give that a shot, but i dont think the Rand function alone will give me the numbers i need...i am looking at either adding an if, and, or statement or using a different function. Have a made it clear what sort of random numbers i am looking for?

For columns F & G the randomness needs to sum to the total of row 13 and those sums need to loosely reflect the decreasing value of column C. I had never altered the calculation mode before so i will give that a shot, but i dont think the Rand function alone will give me the numbers i need...i am looking at either adding an if, and, or statement or using a different function. Have a made it clear what sort of random numbers i am looking for?

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
That's the ticket!!!