+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Randbetween

  1. #1
    Registered User
    Join Date
    08-28-2010
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Randbetween

    Hi All,

    I'm currently using this formula,

    =IF(ISBLANK(A1),"",IF(A1=70,RANDBETWEEN(1,1000),IF(A1=90,RANDBETWEEN(1,800))))

    My problem is, once the number is generated I need it to not change every time the sheet is calculated.
    Last edited by Iandislikesformulas; 08-31-2010 at 08:43 PM.

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Randbetween

    Is this not what it is meant to do?

    From
    http://office.microsoft.com/en-us/ex...005209230.aspx
    Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated
    .

    You could use VBa with the change event for the sheet in question to get a stable result.

    In the Worksheet Module of your chosen Sheet
    Please Login or Register  to view this content.
    Adjust the cell refs to suit.

    Hope this helps
    Last edited by Marcol; 08-28-2010 at 08:18 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Registered User
    Join Date
    08-28-2010
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Randbetween

    yep thats exactly what its sposed to do..... but seeing as though nothing in excel is written for stupid ppl it was the best i could find at the time and the reason i asked for help on here.
    But anyway thanks for the reply, any chance you could put it in more simple terms?

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Randbetween

    Hi Iandislikesformulas

    No offence intended.

    To use the code above.
    1/. In Excel right click on the tab of the sheet you want to use the macro in.
    Select View code this will open the VBa editor with the sheet module.

    2/. Paste all of the given code in the resultant pane/window.

    3/. Save the file and close the VBa editor

    I have put together a demo workbook for you to try.
    Because this book has three examples of possible solutions the code is a little diferent from Post #2.
    Right click on the tab of Sheet1 as above to see the code.

    The code is as easy to read as I can manage and should be fairly self explanatory.

    I hope this helps.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-28-2010
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Randbetween

    Thanks for your help... it seems to be working now

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1