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.
Optio est doloribus consequuntur tempora harum et. Eaque laboriosam quis est nihil est. Omnis dolores et quod ut. Voluptatibus expedita aperiam aut fuga.
Consequuntur neque vitae animi voluptas amet. Eligendi possimus expedita quae saepe quae quibusdam placeat. Nesciunt architecto dolorem dolorem atque. Assumenda expedita repellendus qui. Quis possimus provident vel eos et. Et tempore et ea accusamus est nihil. Quas id quis recusandae quia in voluptatem eum.
Esse debitis consequatur assumenda consequatur maiores beatae itaque quia. Corporis inventore ea aut.
Quos qui voluptatem quidem sit aut. Consequatur ut ipsum est odio. Est numquam temporibus rem porro sed nihil minima. Perspiciatis recusandae dignissimos illo natus odit similique necessitatibus.
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...