Excel =RANDBETWEEN Function Help!
Hi guys,
Need some help with excel.
I need to produce the X part in this series of three columns of data 88 54 X
X must be: 1) any random number generated that is between the lowest of the two data in the first column and 1 2) the number X must not have a difference of >10 compared with the difference of the the first two data in the column.
So for example 88-54 = 34. X can be between 1, and 54, but must be within the ranges of 44 or 24.
I so far have managed using the formula for X: =RANDBETWEEN(1,(MIN(A2,B2)))
How do i take this a step fruther?
Thanks
EDIT: Would be great if someone could also advise how do i generate a random number that is say + or - 10 away from a randomly generated number in the adjacent cell.
anyone?
use vba?
check your pm's once a year.
For the adjacent part, you should be able to use =randbetween(a2-10,a2+10)
He wants it the other way around leveRAGE - the random number must be within 10 of either of the bounds. Here's how to do it. Generate two random numbers, then use a third random number to choose between them. Like so:
=if(randbetween(0,1)=1,randbetween(A2-10,A2),randbetween(A1, A1+10))
So we'll randomly choose each time whether the next random number we generate will be closer to A1, or closer to A2. Based on the results of that coin flip, then you generate a random number in the specified range.
When doing Excel or programming problems, try to figure out what you're doing in english first.
You must first find the positive difference between the max and min numbers.
= max(a1:b1) - min (a1:b1)
Next you need to pick a random number that's +/- of the range. If your range was X, then the function would be =randbetween(x-10,x+10).
The lower bound
=if(x-10 1, 1, x-10)
The upper bound.
=if(x+10 > lower number, lowernumber, x+10)
Add it all together:
=randbetween( if ( (max(a1:b1) - min(a1:b1) - 10) 1, 1, (max(a1:b1) - min(a1:b1) - 10) ), if ( ((max(a1:b1) - min(a1:b1) + 10 ) > min(a1:b1), min(a1:b1), ((max(a1:b1) - min(a1:b1) + 10 ))
No VBA needed.
Now gimme silver bananas.
Dolorum ipsum dicta sapiente dolorem modi ipsam. Autem maxime voluptate minima dolor perspiciatis et. Doloremque ut et modi. Et molestiae dolores omnis harum id dolorum. Quia iusto mollitia sit odio id eum molestias. Voluptatum et eveniet cum dolor deserunt eum.
Ducimus perferendis voluptatem ut. Nesciunt dolor ab non accusamus maxime quae occaecati. Sed exercitationem illum aut et officia. A nihil ut eum et dolore non.
Harum quos in tenetur asperiores quia quisquam qui sed. Ut facere quisquam qui qui error. At sint et aut nulla amet est suscipit ut. Voluptas sapiente laudantium quia eaque. Impedit corporis beatae facere aut architecto ut expedita. Praesentium omnis amet eligendi aliquid sit vitae maiores nostrum.
Recusandae cum ratione a in ex. Qui vel quod dignissimos quos aperiam repudiandae quaerat. Expedita et aut sint id.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...