Personal computing discussed

Moderators: SecretSquirrel, just brew it!

SpotTheCat
Gerbilus Supremus
Topic Author
Posts: 12267
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

### excel and random numbers.

So I want a very simple iteratively generated random number. What I want to do looks like this in excel:
``=random(1,random(1,random(1,100)))``

The average of which should be 12.5. With over 9,000 (heh) values in excel, I keep getting 13.3-13.4ish. What gives? I've used random.org in the past because it is much better, but because of the iterative nature of this I'd rather do it straight in excel. How come the averages aren't even right?

...or did I miss something in statistics...

wibeasley
Gerbil Elite
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

### Re: excel and random numbers.

I get the same average (~13.35081) with this routine in R (the open-source stat program):
``replicationCount <- 1000000sum <- 0for( i in 1:replicationCount ) {  sum <- sum +  runif(n=1, min=1, max=runif(n=1, min=1, max=runif(n=1, min=1, max=100)))}print(sum / replicationCount)``
Do you think the answer should be 12.5 because of 100*(.5^3)? Since the RNGs are nested, I don't think that expression is supposed to be equivalent to when teh RNGs are independent. Someting like:
`` runif(n=1, min=1, max=100) * runif(n=1, min=1, max=100) * runif(n=1, min=1, max=100)``
I've gotta go now, but I'll think about it and try to give a better explaination is some one hadn't done it already.

SpotTheCat
Gerbilus Supremus
Topic Author
Posts: 12267
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

### Re: excel and random numbers.

wibeasley wrote:
I get the same average (~13.35081) with this routine in R (the open-source stat program):
``replicationCount <- 1000000sum <- 0for( i in 1:replicationCount ) {  sum <- sum +  runif(n=1, min=1, max=runif(n=1, min=1, max=runif(n=1, min=1, max=100)))}print(sum / replicationCount)``
Do you think the answer should be 12.5 because of 100*(.5^3)? Since the RNGs are nested, I don't think that expression is supposed to be equivalent to when teh RNGs are independent. Someting like:
`` runif(n=1, min=1, max=100) * runif(n=1, min=1, max=100) * runif(n=1, min=1, max=100)``
I've gotta go now, but I'll think about it and try to give a better explaination is some one hadn't done it already.
Hmmf. Any help would be appreciated. I just figured iterating the average would give me the overall average

I never actually took a stats class, we just learned what applied as we went.

just brew it!
Gold subscriber
Administrator
Posts: 48233
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

### Re: excel and random numbers.

Your problem is that for the average to come out to 12.5, the lower end of the ranges should be 0, not 1.
If the world isn't making sense to you, you're either drinking too much or not drinking enough.

wibeasley
Gerbil Elite
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

### Re: excel and random numbers.

In this situation, a Calc III class might be more helpful. Even without a stats class, you had good intuition. There may be other useful ways to solve your problem, but a triple integral (one for each RNG draw) is one approach:

If you care, the value of x3 is what's being averaged/integrated. The terms inside the parentheses are the uniform PDFs (probabilty density functions). Because the interval is narrower for x3 than for x1, the density (or height) is larger -for a given triplet, 1/x2 >= 1/x1 >= 1/100.

If you think better in code, here's the first equation drawn in R:
``rm(list=ls(all=TRUE)) #Clear any existing variablesreplicationCount <- 1000000sum <- 0for( i in 1:replicationCount ) {  x1 <- runif(n=1, min=0, max=100) #Draw a random uniform score from [0, 100]  x2 <- runif(n=1, min=0, max=x1)  #Draw a random uniform score from [0, x1]  x3 <- runif(n=1, min=0, max=x2)  #Draw a random uniform score from [0, x2]  sum <- sum +  x3}print(sum / replicationCount)``
The second equation corresponds to your excel code; as you can see, the expected value nicely matches your empirical "13.3-13.4ish" estimate. The first equation (with lower bounds of zeros) might be what you were thinking during the initial post. Because the pdfs are uniform, you're initial assumption was probably correct -the terms can be multiplied independently. Be careful though: that won't work if the distributions aren't uniform. If you don't mind me asking, what are you modeling with those uniform distributions?

Tell me if I didn't explain something well. Do you still believe that your excel code was the correct way to express the problem? Was your only concern the discrepency between 12.5 and "13.3-13.4". If so, you're a tenacious tester.

wibeasley
Gerbil Elite
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

### Re: excel and random numbers.

just brew it! wrote:
Your problem is that for the average to come out to 12.5, the lower end of the ranges should be 0, not 1.
This is probably a more helpful for your situation than my long-winded response.

just brew it!
Gold subscriber
Administrator
Posts: 48233
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

### Re: excel and random numbers.

wibeasley wrote:
just brew it! wrote:
Your problem is that for the average to come out to 12.5, the lower end of the ranges should be 0, not 1.

This is probably a more helpful for your situation than my long-winded response.

I think the two answers complement each other quite nicely. I gave the Cliff Notes version, and you gave the detailed theory behind it.
If the world isn't making sense to you, you're either drinking too much or not drinking enough.

Usacomp2k3
Gerbil God
Posts: 21525
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL
Contact:

### Re: excel and random numbers.

I personally prefer randbetween if I'm looking for random integers.

wibeasley
Gerbil Elite
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

### Re: excel and random numbers.

Yea, I didn't consider that SpotTheCat would want only integers. Is that true? I don't see the 'random' excel function that was in the OP. Is that an actual function, or did you just use that name for simplicity?

Usacomp2k3
Gerbil God
Posts: 21525
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL
Contact:

### Re: excel and random numbers.

wibeasley wrote:
Yea, I didn't consider that SpotTheCat would want only integers. Is that true? I don't see the 'random' excel function that was in the OP. Is that an actual function, or did you just use that name for simplicity?

I'm assuming he used the Excel function "rand"

SpotTheCat
Gerbilus Supremus
Topic Author
Posts: 12267
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

### Re: excel and random numbers.

just brew it! wrote:
Your problem is that for the average to come out to 12.5, the lower end of the ranges should be 0, not 1.

ding ding ding, simple mistake. Thanks.

Last edited by SpotTheCat on Tue Mar 10, 2009 5:58 pm, edited 1 time in total.

SpotTheCat
Gerbilus Supremus
Topic Author
Posts: 12267
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

### Re: excel and random numbers.

Usacomp2k3 wrote:
wibeasley wrote:
Yea, I didn't consider that SpotTheCat would want only integers. Is that true? I don't see the 'random' excel function that was in the OP. Is that an actual function, or did you just use that name for simplicity?

I'm assuming he used the Excel function "rand"

No, I did use (and usually do use) randbetween, and I do want only integers.

wibeasley
Gerbil Elite
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

### Re: excel and random numbers.

SpotTheCat wrote:
...and I do want only integers.
What was the lower bound supposed to be? 0 or 1?

### Who is online

Users browsing this forum: No registered users and 2 guests