+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 25

Ranbetween and if

  1. #1
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    106

    Ranbetween and if

    Happy New year to everyone!
    I have a bit of a problem and I was hoping someone could help. The thing is that I am using a formula but I need to add a way that allows the result to be a number that is always either the same or less than the other.

    I am using this formula in the cell H3
    =IF($C$10=1,RANDBETWEEN(1,9),IF($C$10=2,RANDBETWEEN(10,99),IF($C$10=3,RANDBETWEEN(100,999),IF($C$10=4,RANDBETWEEN(1000,9999),RANDBETWEEN(10000,99999)))))

    This formula generates a random number between the numbers in parenthesis which I want to do , but I also need a formula to put in cell J8 that does the same thing plus return any of those numbers either the same or less than the number in H3.

    I am trying to do is to create a worksheet with division problems so when I put that formula in H3 generates a number and to put a formula in J8 that does the same thing but that generates a number that is less than the one generated in H3. I have no idea as to how to do that.

    In short, if H3 gives me a 7 then J8 gives me a 7 or any number less than 7.


    Any help would be greatly appreciated.
    Liz

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2013 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    16,630

    Re: Ranbetween and if

    Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on their reputation star bottom left.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,375

    Re: Ranbetween and if

    Shouldn't it be essentially the same formula, but replace the upper limit of the RANDBETWEEN() functions (9,99,999,...) with H3? =IF($C$10=1,RANDBETWEEN(1,H3),...)
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    Re: Ranbetween and if

    Removed by JT

    Mis-read requirement (again!!)
    Last edited by JohnTopley; 01-03-2018 at 12:59 PM.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    38,996

    Re: Ranbetween and if

    How about instead,

    A
    B
    C
    D
    E
    F
    G
    1
    Digits
    3
    2
    3
    Dividend
    751
    395
    187
    231
    330
    B3: =RANDBETWEEN(10 ^ ($B$1 - 1), 10 ^ $B$1 - 1)
    4
    Divisor
    142
    70
    101
    26
    321
    B4: =RANDBETWEEN(1, B3)
    Last edited by shg; 01-03-2018 at 02:16 PM.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,375

    Re: Ranbetween and if

    Thinking along the same lines as Jon Topley, but with a slightly different interpretation of your OP: =RANDBETWEEN(10^($C$2-1),10^$C$2-1) (make careful note of the parentheses in each argument), and RANDBETWEEN(10^($C$2-1),H3)

    Edit: shg beat me to it -- not that anybody considers it a race or anything.... right?

  7. #7
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Ranbetween and if

    I got lost with SHG and MrShorty because I do not understand the 10^ so I can't make sense of what that formula is doing to be able to replicate. I am attaching a picture of what I am doing.

  8. #8
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Ranbetween and if

    Not sure if it attached the pic
    Attached Images Attached Images

  9. #9
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Ranbetween and if

    The formula you gave me works but for the divisor I need to be able to choose the amount of digits and although the formula works, it gives me a random number that is divisible but it even if I checked that I want it to be a 3 digit dividend by 2 digit divisor for example, it returns a divisor of any amount of digits as the dividend

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    9,375

    Re: Ranbetween and if

    If the exponentiation is confusing, then you can use the IF(...) structure I suggested in post #3. =IF($C$10=1,RANDBETWEEN(1,H3),...).

    (As basic as exponents are to math instruction, there could be some value in studying exponentiation for your own benefit until you understand how we used exponentiation to simplify your requirement: http://www.purplemath.com/modules/exponent.htm )

  11. #11
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Ranbetween and if

    Thanks, I realized what it meant after trying the formula. The only problem is that it still overwrite the amount of digits I selected. Using your formula, when I select 3 digits for the first number and a 2 digit for the second number, the formula gives me for the second number a 1 or 2 digit no matter if I put that I wanted a 2 digit.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    Re: Ranbetween and if

    why not use this


    =RANDBETWEEN(10^($C$8-1),10^$C$8-1) for DIVIDEND with c8-=3


    =RANDBETWEEN(10^($C$10-1),10^$C$10-1) for DIVISOR with c10=2

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

    Re: Ranbetween and if

    Using your formula, when I select 3 digits for the first number and a 2 digit for the second number, the formula gives me for the second number a 1 or 2 digit no matter if I put that I wanted a 2 digit.
    Example? Mine should give 2 digit numbers for both. I hadn't noticed before, but shg's formula for divisor could give a one digit number as his includes 1 as the lower limit of divisor's RANDBETWEEN() function. It sounds like this could be in error (I don't know if he just misunderstood your requirement or if that is a simple typo). Make sure you understand what we are suggesting well enough to "fix" our mistakes when we make them.

  14. #14
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    106

    Re: Ranbetween and if

    With that formula if I select 2 digits for the first and 2 digits for the second it occasionally gives a number greater than the first

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,527

    Re: Ranbetween and if

    You need to use something like

    RANDBETWEEN(10^($C$2-1),H3-1)

    where H3 is your DIVIDEND value

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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