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.

pawn

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

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.

trogulj

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

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.

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)?

franzino

you need a multivariate normal distribution, not uniform or lognormal