Random Number

[Chris Davison]

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

[Ian Mac]

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 ]

[Chris Davison]

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 ]

[Ian Mac]

Why just one formula?

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

[Ian Mac]

BLIMEY!! a little slow on your PS. cripes! I thought she'd go on and on.

[Jack in the UK]

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(...

[Dave Hawley]

Hi Chris


This works sometimes )

=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.

[eliW]

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

[eliW]

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 ]

[Aladin Akyurek]

On 2002-03-31 02:36, eliW wrote:

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