# Ranbetween and if

1. ## 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. ## 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.

3. ## 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),...)

4. ## Re: Ranbetween and if

Removed by JT

5. ## Re: Ranbetween and if

 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)

6. ## 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. ## 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. ## Re: Ranbetween and if

Not sure if it attached the pic

9. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Ranbetween and if

You need to use something like

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

where H3 is your DIVIDEND value

Page 1 of 2 1 2 Last

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

#### 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