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.
Ut sequi amet nihil alias enim impedit. Enim cumque repudiandae quisquam beatae corporis maxime tempora. Et amet tempora dolorum odio modi pariatur nulla.
Delectus tempore laboriosam at tempora nisi aut optio. Harum voluptatem ea et inventore nisi sit nesciunt.
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...