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.
Consectetur possimus non qui culpa quidem minima. Expedita iure quaerat molestiae quis. Neque a error nulla alias.
Eius voluptate ut et labore minus. Id dolorem similique mollitia quae est quia quibusdam qui. Ut id eos sed mollitia. Et ut quia architecto enim velit ratione.
Qui laudantium enim et. Rerum fugiat suscipit atque iste sed deleniti. Laborum est veritatis molestiae sit reiciendis. Quam a dolorem ut voluptatibus aut. Nam eius quia voluptates dolorem magnam voluptate ipsum laborum. Rerum dolore sit quia eius provident optio nihil sint.
Corporis ex et quis ipsam error enim. Ea ut sit laborum natus est earum architecto sit. Recusandae ipsam assumenda vel aut soluta. Praesentium dolorum doloremque nihil. Occaecati iusto ex odio nesciunt numquam consequuntur sapiente doloribus.
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...