+ Reply to Thread
Results 1 to 25 of 25

Ranbetween and if

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

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,335

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    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 Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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
    15,802

    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
    113

    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
    113

    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
    113

    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
    15,802

    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
    113

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    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
    15,802

    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
    113

    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
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Ranbetween and if

    You need to use something like

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

    where H3 is your DIVIDEND value

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

    Re: Ranbetween and if

    John, the problem in all the formulas I have tried is that I need to be able to select how many digits I want the number to have in the divisor that at the same time is less than the dividend. In all the formulas i get to have a number that is less but I can't choose the amount of digits.
    Look at the picture, I use the formula and even though I selected to have a 3 digit and the second a 2 digit it returned a 3 digit

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

    Re: Ranbetween and if

    Example of the sheet
    Attached Images Attached Images

  18. #18
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Ranbetween and if

    Post your real file; you have all the information to make it work but you will (likely) need an IF statement to manage the various conditions.

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Ranbetween and if

    See the attached ...
    Attached Files Attached Files

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

    Re: Ranbetween and if

    Here is the original file
    Attached Files Attached Files

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Ranbetween and if

    in H3

    =RANDBETWEEN(10^($C$7-1),10^$C$7-1)

    in J3

    =IF($C$7<>$C$10,RANDBETWEEN(10^($C$10-1),10^$C$10-1),RANDBETWEEN(10^($C$10-1),$H3-1))

    the last does not test if C7 < c10 (although I don't see why that condition should not exist)!!

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

    Re: Ranbetween and if

    The formula works allowing me to choose the amount of digits for each number but do you know why it is still giving me some number with the divisor grater than the dividend like the two marked in the picture?

    Never mind! I found it was a mistake I made when writing the formula.
    Attached Images Attached Images
    Last edited by lizsantiago07; 01-03-2018 at 05:18 PM.

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Ranbetween and if

    Because you have changed all the formulas (using the ones I gave) in every cell ????

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

    Re: Ranbetween and if

    It was my mistake. THank you so much for all your help!!!!

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Ranbetween and if

    You're welcome.

+ 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 do Ranbetween and exclude 0?
    By Tommy1005 in forum Excel General
    Replies: 1
    Last Post: 05-09-2011, 04:28 AM

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