Excel Question... numbers, average, positive, generate

maxmeomeo

So I want to generate series of 26 random numbers many times, and I want their average of each series to be 5. I’d like for the numbers to be negative as well as positive, how do I do that? Rand() doesn’t seem to work well with generating negative nos with equal probability as positive nos.

franzino

you need a multivariate normal distribution, not uniform or lognormal

yospaghetti

Thanks for your responses, I used =(RAND()*(7–7)+-7+5)/100+1 to create random values with expected value of 5, so the arithmetic mean of a 26 no. series would roughly equal to 5.
But as pointed out, this series is uniformly distributed, but for portfolio returns, I suppose we should use a lognormal dist. Should I do LOGINV(probability,5, standard deviation)?

atate2007

ohai Wrote:
——————————————————-
What sort of distribution do you need? Rand()
produces uniform random variables. However, you
can do various things with the formula to produce
normal, log-normal or other distributions.
It’s refreshing to see your comment. It drives me crazy when distribution is not considered in random number generation.

trogulj

Also, do you strictly need the average of each 26-unit set to equal 5? Or does the expected value just equal 5?

LiquidAssets10

What sort of distribution do you need? Rand() produces uniform random variables. However, you can do various things with the formula to produce normal, log-normal or other distributions.

pawn

a*rand()+b will generate random numbers in the interval (b, a+b)