Random Number

[cpg84]

is there a formula that can be used instead of randbetween(min,max), or rand() that would return a random number that wont change everytime you do something else on the spreadsheet?

 

[Smitty]

Not really.

RAND and it's offshoots are VOLATILE (check the helpfile) functions, which means that they calculate each time the sheet does.

But you can use some event code to do what you want, you just need to specify when/where/how you want the number to change.

Post back and someone'll come up with something for you.

Smitty

 

[tusharm]

Depending on (1) how comfortable you are with VBA and (2) how comfortable you are using a User Defined Function, you can write a VBA wrapper function to control how you get random numbers.

Function VBARnd(Optional ByVal Volatile As Boolean = True)
    Application.Volatile Volatile
    VBARnd = Rnd()
    End Function

Use it as =VBARnd(C1) where C1 contains TRUE or FALSE. If it contains TRUE, the function will recalculate each time you press F9. If not, not.

You can use VBARnd as you would the Excel function RAND. So, to generate integer numbers between A and B, both inclusive, use =INT(VBARnd(C1)*(B-A+1))+A

is there a formula that can be used instead of randbetween(min,max), or rand() that would return a random number that wont change everytime you do something else on the spreadsheet?

 

[cpg84]

thanks, I think I am just gonna have a random number generator that recalculates on the side and just type in the values manually in each box.

It's just a temporary thing until the next NBL season starts.