#1
|
|||
|
|||
Rand 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,RANDBETWEE N(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 according to the amount of digits selected in C10 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. What I am trying to do is to create a worksheet with division problems so when I put that formula in H3 generates a number according to the amount of digits entered in C7 and to put a formula in J8 that does the same thing according to the amount of digits entered in C10 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 I want a 3 digit number in H3 and it gives me a 318 and if I want a 2 digit number in J8 then the result should be any number but If I wanted to be a 3 digit number then it should give me a a 318 or any 3 digit number less or equal to 318 Any help would be greatly appreciated. Liz |
#2
|
||||
|
||||
You can add a MIN() function around it so that it takes the Minimum of the RANDBETWEEN() result or the result in H3.
=MIN($H$3,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)))) )) |
#3
|
||||
|
||||
You can also shorten the formulas to:
=RANDBETWEEN(CHOOSE($C$10,1,10,100,1000),CHOOSE($C $10,9,99,999,9999)) and =MIN($H$3,RANDBETWEEN(CHOOSE($C$10,1,10,100,1000), CHOOSE($C$10,9,99,999,9999))) |
#4
|
|||
|
|||
That works but it occasionally gives me a divisor greater than the dividend resulting in a division that would total a percentage. is there a way that have the formula to always give me a divisor that is always less. This is an example of one of the returns it gave me.
1263 ÷ 2512 = 0.50 |
#5
|
||||
|
||||
With the formulas I gave that should not be possible, unless you are not referencing the right cells. Please check the cell references in both formulas to ensure they are looking at the correct cells.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
+IF(RAND()........) confused newbie | jeffk | Excel | 6 | 05-28-2013 08:03 AM |