Random Number

cpg84

Active Member
Joined
Jul 16, 2007
Messages
264
Platform
  1. Windows
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.
Code:
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

Active Member
Joined
Jul 16, 2007
Messages
264
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,647
Messages
5,931,207
Members
436,783
Latest member
darkwingduck1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top