How to generate random numbers and have a function reference the formula.

[nooby]

What I am trying to accomplish

Cell A7 uses =ROUND((RAND()*(0-54)+54),0) to give me rounded whole number between 1 and 54


Cell B7 uses this function =IF(A7<20,ROUND((RAND()*(0-19)+19),0),IF(A7>19,ROUND((RAND()*(0-73)+73),0),IF(A7>73,ROUND((RAND()*(0-127)+127),0))))


2 Problems.

First my function works it is not calculating correctly. This is what I really need to find out...

If A7 < 20 I want to see a random whole number between 1-19, If A7 > 19 BUT < 73 I want to see a random whole number between 1 - 73, and last if A7 > 73 I want to see a random whole number between 1-127.


Second, When ever I enter data into a blank cell (any blank cell on the worksheet) the function will run again and provide new results. Is there any way to lock it down (maybe just columns A&B) so that it is less volatile and will not randomly generate numbers every time the worksheet is modified?


Any help with either question would be appreciated

 

[fred]

first of all, make your life easier on A7


=randbetween(1,54)

 

[oldchippy]

Hi nooby,


Welcome to the forum. I don't understand the third part, where you want a number generated between 1-127 if A7 is > 73, when the function in A7 only goes up to 54?


But if that's just a typo try this


=IF(A7<20,RANDBETWEEN(1,19),IF(AND(A7>19,A7<73),RANDBETWEEN(1,73),IF(A7>73,RANDBETWEEN(1,127),"")))


For freezing the numbers you will need VBA for that and that's not my expertise, but I'm sure someone else can help

 

[fred]

you have set your A7 limit from 1 to 54. you will never get a random number beyond 54. that's the first problem.


and your second problem on locking. if you are using excel 2007 like me,


go to formulas tab, go to "calculation options", pick "Manual". the next time you need to re-calculate the random number. hi-light both A7 and B7 and hit the F2 then enter twice.

 

[nooby]

Oldchippy, Thanks for reply.


That was a typo indeed. The values were <20 returns 1-19, >19 or <37 returns 1-73, and >36 returns 1-127. All the returns are random.


=IF(A7<20,RANDBETWEEN(1,19),IF(AND(A7>19,A7<37),RANDBETWEEN(1,73),IF(A7>36,RANDBETWEEN(1,127),"")))


I use this function and get an invalid name error?


Fred, I am using Office 2003 , I did turn off Automatic calculations and find that pressing 'F9' will re-calculate. That will work for me. I just need help with the above function.


Thanks everyone for helping me this far. We're almost there

 

[oldchippy]

Hi,


With Excel 2003 you need to install the Analysis Tool Pak see here to use RANDBETWEEN()


http://office.microsoft.com/en-us/excel-help/load-the-analysis-toolpak-HP001127724.aspx

 

[nooby]

Oldchippy,


I installed the Add-in. When in Data analysis I should select the Random Number Generation, correct? What values should I use for each field?


Thanks!

 

[oldchippy]

Hi,


Now you've installed it all you need to do now is to copy and paste the formulas into the cells

 

[nooby]

Fantastic...I forgot that I had automatic calculation turned off ;P...Looks like everything is working good now


Thanks for all the help!!