+ Reply to Thread
Results 1 to 3 of 3

Question about a "randbetween" substitute formula

  1. #1
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    2

    Question about a "randbetween" substitute formula

    I often use the [Randbetween()] function to generate a random number between 0 and a number in a cell that changes. Normally Randbetween works just fine for this, but sometimes the number being referenced ends up being a negative number, resulting in the 0 and negative number being out of order, for example "Randbetween(-20,0)". This results in a value error.

    The workaround I have found for this is to use this formula where "A1" is the cell being referenced
    "RAND()*(0-A1)+A1"
    Am I correct in thinking that this is achieving the same result that a "randbetween" would without the need to have the bottom and top values placed correctly within the formula?
    If not how else can I achieve this? The only other way i've thought of is
    "IF(A1<0, RANDBETWEEN(A1,0),RANDBETWEEN(0,A1))"
    but that's very text and command heavy for something that I feel should be fairly simple.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Question about a "randbetween" substitute formula

    Your RAND()... function seems like it should work -- except that it will return random real numbers between 0 and A1 where the RANDBETWEEN() function returns random integers. You may need to think through what your doing so you know the difference between random reals and random integers for your project. If integers are needed, you could nest this inside of an INT() or ROUND() or similar function. If you need random reals, you probably need to stay away from the RANDBETWEEN() function.

    I don't know if it is any better than either of your solutions, but maybe =RANDBETWEEN(MIN(A1,0),MAX(A1,0))?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-07-2019
    Location
    USA
    MS-Off Ver
    2013
    Posts
    2

    Re: Question about a "randbetween" substitute formula

    I had not considered the dilemma of return real numbers only, it does present a problem with my equation.
    However.....

    =RANDBETWEEN(MIN(A1,0),MAX(A1,0))

    Is brilliant and solves my problem exactly! Very well done and thanks for making my first post here a success!
    I'm not sure if the Excel devs frequent these forms but a simple function that returns a random value between two numbers regardless of which one is top or bottom would be very convenient if it's doable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to Substitute "11" with "11th" without ending up with "11th11th"?
    By bcianni in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-21-2019, 03:20 PM
  2. [SOLVED] Indirect "Substitute(Substitute(" not working for multiple spaces
    By Ochimus in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2018, 04:01 PM
  3. Need help in using =INDIRECT(SUBSTITUTE($H3," ","_")) formula
    By enterdelete123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2018, 01:39 AM
  4. [SOLVED] Help on "IF", "FIND", "SUBSTITUTE", & "LEFT/RIGHT" FUNCTIONS
    By pooja135 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-20-2018, 03:56 PM
  5. SUBSTITUTE question replacing "." with ":"
    By russkris in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-18-2017, 05:26 AM
  6. [SOLVED] How to insert + sign in front of every word =Substitute(A3," "," +")
    By inayet in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2013, 09:41 PM
  7. Replies: 5
    Last Post: 06-26-2006, 09:23 PM

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