excel and random numbers.

From Visual Basic to GNU C, this is the place to talk programming.

Moderators: SecretSquirrel, just brew it!

excel and random numbers.

Postposted on 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:
Code: Select all
=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...
SpotTheCat
Gerbilus Supremus
 
Posts: 12262
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

Re: excel and random numbers.

Postposted on Mon Mar 09, 2009 5:53 pm

I get the same average (~13.35081) with this routine in R (the open-source stat program):
Code: Select all
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:
Code: Select all
 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.
wibeasley
Gerbil Elite
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: excel and random numbers.

Postposted on 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):
Code: Select all
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:
Code: Select all
 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.
SpotTheCat
Gerbilus Supremus
 
Posts: 12262
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

Re: excel and random numbers.

Postposted on 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.
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37829
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: excel and random numbers.

Postposted on 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:
Code: Select all
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
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: excel and random numbers.

Postposted on 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.
wibeasley
Gerbil Elite
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: excel and random numbers.

Postposted on 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:
(this space intentionally left blank)
just brew it!
Administrator
Gold subscriber
 
 
Posts: 37829
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

Re: excel and random numbers.

Postposted on Tue Mar 10, 2009 11:33 am

I personally prefer randbetween if I'm looking for random integers.
Usacomp2k3
Gerbil God
 
Posts: 21311
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL

Re: excel and random numbers.

Postposted on 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?
wibeasley
Gerbil Elite
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK

Re: excel and random numbers.

Postposted on 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"
Usacomp2k3
Gerbil God
 
Posts: 21311
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL

Re: excel and random numbers.

Postposted on 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
 
Posts: 12262
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

Re: excel and random numbers.

Postposted on 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.
SpotTheCat
Gerbilus Supremus
 
Posts: 12262
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

Re: excel and random numbers.

Postposted on 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?
wibeasley
Gerbil Elite
Gold subscriber
 
 
Posts: 952
Joined: Sat Mar 29, 2008 3:19 pm
Location: Norman OK


Return to Developer's Den

Who is online

Users browsing this forum: No registered users and 4 guests