Random number, generates only once, 4 digits!

W

wpryan

Well-known Member
Joined
May 26, 2009
Messages
534
Office Version
  1. 365
Platform
  1. Windows
I'm trying to generate a series of random numbers. the criteria is that the numbers should only be generated once, and must have 4 digits (e.g. between 0000 and 9999).
I've tried the following in Excel:
Code: 
=TRUNC((RAND()*(9999-0)+0),4)
but it still will show a number less than 1000 as 3 digits (or less than 100 as 2 etc), not 4. Also, it calculates with every change to the form.
Would anyone care to help? thanks.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Change the code to

Code: 
=TRUNC(RAND()*9999,4)

You don't need all those + and - of zero's
 
Upvote 0
Thanks for that. How can I get it to calculate only once?
 
Upvote 0
If you need the random number's range to be within 1000-9999

Code: 
=TRUNC((RAND()*(9999-1000)+1000),4)
 
Upvote 0
I'm not sure I know how to do it. The purpose of this is to generate a 4-digit PIN on a quarterly basis. the PIN would be in column B, starting row 4, and will grow based on a date entry in column A. Even though I've written some VBA code I'm pretty sure I'd have to write it as a loop but I'm not good with that. ...do you mind? Thanks in advance.
 
Upvote 0
Well, if you mean by just generating a random number just once.

This is it:

Code: 
    Range("A1").Value = CInt(((9999 - 1000) * Rnd) + 1000)


I'm not sure what you're trying to ask, would you like to expand on it?
 
Upvote 0
Sorry for the delay and thanks for your help. According to your code, range A1 will get a random number. What I was looking for was code that, if range B1 is empty, A1 will also be empty, else if B1 has something in it then the code fires. The same for the next row. I made simple "if then" construct for 2 rows but it seems that when the second line is calculated the first one fires again as well.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Formula to convert 4 digits number to digits number !!
Replies
9
Views
206
serge
S
N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
378
Peter_SSs
Peter_SSs
B
  • Question
Random Letter and Number Generator
Replies
4
Views
751
Joe4
Joe4
T
  • Solved
Random number generator button
Replies
3
Views
735
Georgiboy
Georgiboy
T
  • Question
Multiple filters on filtered list
Replies
1
Views
215
Cubist
Cubist

Forum statistics

Threads
1,214,951
Messages
6,122,446
Members
449,083
Latest member
Ava19

Share this page

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
Back
Top