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.
Occaecati dicta id ullam harum. In incidunt suscipit harum iste neque. Labore aut quis non autem. Amet voluptatum beatae et qui expedita neque qui quae. Dolores qui vel adipisci. Nulla asperiores occaecati dolore explicabo voluptas.
Quo quia nihil libero enim ad nihil ratione. Iure ut iure esse ea.
Nisi esse eos sunt. Molestiae voluptas qui aut. Quos aut et hic aut rem.
Repellat voluptatem minus quia mollitia. Mollitia sint aliquam eligendi quia reprehenderit velit maiores. Non hic assumenda est itaque ea tempore. Et quis vel ex iste. Distinctio dignissimos adipisci earum est minima quae ut. Ut est temporibus blanditiis dolorum et aut et.
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...