Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Random Number

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Why just one formula?

    A UDF of a similar nature would do the job, but I'm assuming you don't want that?
    "Have a good time......all the time"
    Ian Mac

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BLIMEY!! a little slow on your PS. cripes! I thought she'd go on and on.
    "Have a good time......all the time"
    Ian Mac

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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(...
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •