Forum Discussion
Generate Random number with conditions
You say you'd like "to generate a random number with mean 48 when Time is below 100. Mean 42 when time is between 1000 and 1700. Mean...." [the text disappeared after that last "Mean"]
I've placed the emphasis in that quote from you on "A random number." That means a single number. A single number doesn't go conceptually with "mean" however. A mean would come from a set of numbers.
And random numbers in Excel are generated between a low number and a high number, not around a "mean"......
So why don't we go back to the full context here: what are you actually trying to accomplish with that request? Why differentiate the criteria for the randomness in that way? etc.
That said, you could easily use conditionals and multiply:
=2*RAND()*IFS( [time]<100, 48, AND([time]>1000,[time]<1700), 42, .... )
so basically RAND() will give a number 0 to 1 so multiplying by 2 makes it between 0 and 2 then multiplying by the "mean" you want will make it centered on that intended mean (range 0 to 2xmean) of you could also:
=(RAND()-0.5) * [range] + IFS( [time]<100, 48, AND([time]>1000,[time]<1700), 42, .... )
In this version you can set a [range] so it will be centered around the mean and be +/- that [range] value.
NOTE neither case will GUARANTEE a mean of the desired value just have random numbers that are generate 'centered' on that mean.
Also, this will generate a "non-normal" distribution because it will equally likely to any number in the range and not have a "normal" distribution.
the whole intended distribution needs to be addressed and if the mean MUST be = to the intended mean.
mtarler wrote:
I believe what they are looking for is to generate a column of random numbers that are dependent on the time column so if you group all the generated random numbers that are next to a time <100 those random numbers will have a mean of 42.
And I think you're right, Matt, with the next batch having a mean of 48...and so forth.
A point I didn't make, however--because it all needs better definition (IMHO)--is that a group of random numbers with a mean of 42 just isn't going to be all that much different from a group of random numbers with a mean of 48--there just isn't enough variance between those two "means," UNLESS the range of randomness is greatly restricted and the random numbers aren't integers... so, I repeat, greater definition is required!!