Personal computing discussed

Moderators: renee, SecretSquirrel, just brew it!

 
SpotTheCat
Gerbilus Supremus
Topic Author
Posts: 12292
Joined: Wed Jan 29, 2003 12:47 am
Location: Minnesota

excel and random numbers.

Mon Mar 09, 2009 5:21 pm

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.

Mon Mar 09, 2009 5:53 pm

I get the same average (~13.35081) with this routine in R (the open-source stat program):
replicationCount <- 1000000
sum <- 0
for( 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: 12292
Joined: Wed Jan 29, 2003 12:47 am
Location: Minnesota

Re: excel and random numbers.

Mon Mar 09, 2009 8:21 pm

wibeasley wrote:
I get the same average (~13.35081) with this routine in R (the open-source stat program):
replicationCount <- 1000000
sum <- 0
for( 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!
Administrator
Posts: 54500
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: excel and random numbers.

Mon Mar 09, 2009 9:31 pm

Your problem is that for the average to come out to 12.5, the lower end of the ranges should be 0, not 1.
Nostalgia isn't what it used to be.
 
wibeasley
Gerbil Elite
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: excel and random numbers.

Mon Mar 09, 2009 9:37 pm

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:
Image
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 variables
replicationCount <- 1000000
sum <- 0
for( 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.

Mon Mar 09, 2009 9:41 pm

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!
Administrator
Posts: 54500
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: excel and random numbers.

Mon Mar 09, 2009 9:51 pm

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. :wink:
Nostalgia isn't what it used to be.
 
Usacomp2k3
Gerbil God
Posts: 23043
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL
Contact:

Re: excel and random numbers.

Tue Mar 10, 2009 11:33 am

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.

Tue Mar 10, 2009 11:46 am

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: 23043
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL
Contact:

Re: excel and random numbers.

Tue Mar 10, 2009 11:48 am

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: 12292
Joined: Wed Jan 29, 2003 12:47 am
Location: Minnesota

Re: excel and random numbers.

Tue Mar 10, 2009 5:38 pm

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.

:oops: :oops: :oops:
Last edited by SpotTheCat on Tue Mar 10, 2009 5:58 pm, edited 1 time in total.
 
SpotTheCat
Gerbilus Supremus
Topic Author
Posts: 12292
Joined: Wed Jan 29, 2003 12:47 am
Location: Minnesota

Re: excel and random numbers.

Tue Mar 10, 2009 5:51 pm

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.

Tue Mar 10, 2009 8:42 pm

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 1 guest
GZIP: On