Random Number

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Evening all,

how can I return a single random number from the set : 1 to 10 and 20 to 30

ie

8 would be okay
2 would be okay
23 would be okay
29 would be okay

but 15 would not be okay
and 18 would not be okay

Just one random number from those ranges 1-10, 20-30

I'm pretty familiar with =RAND and also have the Analysis Toolpak installed

many thanks
Chris
:)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
this is not properly tested but,
in 1, say A1, cell put:

=RANDBETWEEN(1,30)

in the next cell put:

=IF(AND(A1>10,A1<=15),10-RANDBETWEEN(0,10),IF(AND(A1>15,A1<20),20+RANDBETWEEN(0,10),A1))

As I say not fully tested, let me know if it does the job for you.

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-03-30 09:59
 
Upvote 0
oooops

sorry Ian, I know how irritating this can be :

I meant in a single formula

(Gawd, I can't believe I just did that)

thanks mate
Chris
:)



ps - watchign the news ? Queen Mum dead :(
This message was edited by Chris Davison on 2002-03-30 10:02
 
Upvote 0
Why just one formula?

A UDF of a similar nature would do the job, but I'm assuming you don't want that?
 
Upvote 0
Chris

Come onyour my mate you know this will not be done unless some UDF sits behind so:

ChrisD_Lotter(range1... range6) works

No function does this in dafult MS selectable but what about if i slip in a COM file.. (thats and add in in the text i mean before i get shot) that would do it!

need the answer eh!

OK what you do cleaverly nest the formula so essentially one fornula wil that not do ?

IE IF(...
 
Upvote 0
Hi Chris


This works sometimes :eek:)

=TRIM(MID(RANDBETWEEN(1,10) & " " & RANDBETWEEN(20,30),RANDBETWEEN(1,3),RANDBETWEEN(1,2)))


Tends to return a blank at times though :( But may give you something to work on.
 
Upvote 0
Hi Chris,
One formula:
=IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(0,10),RANDBETWEEN(20,30))
This would work properly (true random) only if the two ranges are equal.
Regards,
Eli
 
Upvote 0
On 2002-03-30 22:16, eliW wrote:
Hi Chris,
One formula:
=IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(0,10),RANDBETWEEN(20,30))
This would work properly (true random) only if the two ranges are equal.
Regards,
Eli
A little change which allow randomizing 2 different size of ranges, for example:
1-5,20-30
=IF(RANDBETWEEN(1,15)<=5,RANDBETWEEN(1,5),RANDBETWEEN(20,30))
Eli
This message was edited by eliW on 2002-03-31 02:38
 
Upvote 0
On 2002-03-31 02:36, eliW wrote:
On 2002-03-30 22:16, eliW wrote:
Hi Chris,
One formula:
=IF(RANDBETWEEN(0,1)=0,RANDBETWEEN(0,10),RANDBETWEEN(20,30))
This would work properly (true random) only if the two ranges are equal.
Regards,
Eli
A little change which allow randomizing 2 different size of ranges, for example:
1-5,20-30
=IF(RANDBETWEEN(1,15)<=5,RANDBETWEEN(1,5),RANDBETWEEN(20,30))
Eli
This message was edited by eliW on 2002-03-31 02:38

Eli,

Your first formula (slightly modified here) appears to me elegant and satisficing:

=IF(RANDBETWEEN(0,1),RANDBETWEEN(1,10),RANDBETWEEN(20,30))

The size of<1,10> is eq to that<20,30>. Is there something I'm missing?

Regards,

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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