## 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.

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

### Re: excel and random numbers.

I get the same average (~13.35081) with this routine in R (the open-source stat program):
Code: Select all
`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:
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

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

### Re: excel and random numbers.

wibeasley wrote:I get the same average (~13.35081) with this routine in R (the open-source stat program):
Code: Select all
`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:
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: 12264
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

### 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.
The years just pass like trains. I wave, but they don't slow down.
-- Steven Wilson
just brew it!
Gold subscriber

Posts: 40816
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

### 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:
Code: Select all
`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.
wibeasley
Gerbil Elite

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

### 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.
The years just pass like trains. I wave, but they don't slow down.
-- Steven Wilson
just brew it!
Gold subscriber

Posts: 40816
Joined: Tue Aug 20, 2002 10:51 pm
Location: Somewhere, having a beer

### Re: excel and random numbers.

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

Posts: 21391
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL

### 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?
wibeasley
Gerbil Elite

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

### 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"
Usacomp2k3
Gerbil God

Posts: 21391
Joined: Thu Apr 01, 2004 4:53 pm
Location: Orlando, FL

### 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

Posts: 12264
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.
SpotTheCat
Gerbilus Supremus

Posts: 12264
Joined: Wed Jan 29, 2003 12:47 am
Location: a regular hole

### Re: excel and random numbers.

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

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