Multiple coin flips in a single cell

[igor eduardo küpfer]

I have three columns of data. The first column is the ID of a particular
Coin Flipper. The second is the number of coin flips each Coin Flipper
flips. The third column is the probability of each flipper flipping a
heads—these probabilities are different for each Flipper, but the same for
each Flipper's flip. I'm looking for a function that will display the number
of heads flipped for each Flipper, based on the data in the second and third
columns.

Wow. That came out more complicated than it should have been. Let me try
again.

A B C D
--------------------------------------
1 | ID #Flips p(Heads) #Heads
2 | 1 5 0.5
3 | 2 2 0.2
4 | 3 10 0.3
5 | 4 2 0.4
6 | 5 4 0.6

These are not necessarily fair coins. Some are weighted towards heads, so
that more than 50% of the flips will turn out to be heads, some are weighted
towards tails. I need to flip the coins for each flipper, and count the
heads.

For example, for the first flipper, I can enter the following in D2 and get
my result:

= (RAND()<C2) + (RAND()<C2) + (RAND()<C2) + (RAND()<C2) + (RAND()<C2)

But each of my flippers flips a different number of coins. I don't want to
have to manually enter "(RAND()<C2)" for each flip into the cell in column
D, and I don't want to have to use extra columns for individual flips[*]. Is
there a function or formula I can enter using a single cell that will flip
the exact number of coins? Ideally there'd be a function that looks like
this:

= COINFLIP(Number_Flips, Probability_Heads)

but any old solution would do.

[* The reason I don't want to use extra columns is that some of my flippers
flip hundreds of coins. That's too many columns for me to take up.]
--

all the best,
ed

Epitome:
Nice kid, but about as sharp as a sack of wet mice.
Email:
edkupfer. It's a gmail addy.

 

[Richard Buttrey]

I have three columns of data. The first column is the ID of a particular
Coin Flipper. The second is the number of coin flips each Coin Flipper
flips. The third column is the probability of each flipper flipping a
heads—these probabilities are different for each Flipper, but the same for
each Flipper's flip. I'm looking for a function that will display the number
of heads flipped for each Flipper, based on the data in the second and third
columns.

Wow. That came out more complicated than it should have been. Let me try
again.

A B C D
--------------------------------------
1 | ID #Flips p(Heads) #Heads
2 | 1 5 0.5
3 | 2 2 0.2
4 | 3 10 0.3
5 | 4 2 0.4
6 | 5 4 0.6

These are not necessarily fair coins. Some are weighted towards heads, so
that more than 50% of the flips will turn out to be heads, some are weighted
towards tails. I need to flip the coins for each flipper, and count the
heads.

For example, for the first flipper, I can enter the following in D2 and get
my result:

= (RAND()<C2) + (RAND()<C2) + (RAND()<C2) + (RAND()<C2) + (RAND()<C2)

But each of my flippers flips a different number of coins. I don't want to
have to manually enter "(RAND()<C2)" for each flip into the cell in column
D, and I don't want to have to use extra columns for individual flips[*]. Is
there a function or formula I can enter using a single cell that will flip
the exact number of coins? Ideally there'd be a function that looks like
this:

= COINFLIP(Number_Flips, Probability_Heads)

but any old solution would do.

[* The reason I don't want to use extra columns is that some of my flippers
flip hundreds of coins. That's too many columns for me to take up.]

Try the following as a user defined function

Public Function CoinFlip(Number_Flips As Integer, Probability_Heads As
Double)

Dim Flip As Double
Dim No_Heads As Integer

For Number_Flips = 1 To Number_Flips
Flip = Rnd
If Flip < Probability_Heads Then
No_Heads = No_Heads + 1
End If
Next

End Function

Then type

= CoinFlip(B1,C1) into D1 and copy down


HTH




Type =Coinflip(B1,C1) in D1 and copy down



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[igor eduardo küpfer]

In microsoft.public.excel.worksheet.functions on Mon, 12 Sep 2005 21:01:27

Try the following as a user defined function

Public Function CoinFlip(Number_Flips As Integer, Probability_Heads As
Double)

Dim Flip As Double
Dim No_Heads As Integer

For Number_Flips = 1 To Number_Flips
Flip = Rnd
If Flip < Probability_Heads Then
No_Heads = No_Heads + 1
End If
Next

End Function

Richard, thanks for the response. I tried function you've given me, but it
returns only zeros. Did I do something wrong?
--

all the best,
ed

Epitome:
Nice kid, but about as sharp as a sack of wet mice.
Email:
edkupfer. It's a gmail addy.

 

[Guest]

He's close, but a couple typo's left the function not working properly, this
will work:

Public Function CoinFlip(Number_Flips As Integer, Probability_Heads As Double)

Dim Flip As Double
Dim No_Heads As Integer

For i = 1 To Number_Flips
Randomize
Flip = Rnd
If Flip < Probability_Heads Then
No_Heads = No_Heads + 1
End If
Next i
CoinFlip = No_Heads
End Function

 

[JE McGimpsey]

Richard forgot to make this the last line in his function (before "End
Function"):

CoinFlip = No_Heads

Note that this will calculate once only (unless you do a full recalc on
the worksheet). If you want it to recalculate every time a formula on
the sheet is calculated, add Application.Volatile at the beginning of
the function. This is functionally equivalent:

Public Function CoinFlip(NumFlips As Long, pHeads As Double) As Long
Dim i As Long
Dim nHeads As Long
Application.Volatile
For i = 1 To NumFlips
nHeads = nHeads - (Rnd < pHeads)
Next i
CoinFlip = nHeads
End Function

 

[igor eduardo küpfer]

Works great. Thanks to all who responded.
--

all the best,
ed

Epitome:
Nice kid, but about as sharp as a sack of wet mice.
Email:
edkupfer. It's a gmail addy.

 

[Harlan Grove]

I have three columns of data. The first column is the ID of a
particular Coin Flipper. The second is the number of coin flips
each Coin Flipper flips. The third column is the probability of
each flipper flipping a heads-these probabilities are different
for each Flipper, but the same for each Flipper's flip. I'm
looking for a function that will display the number of heads
flipped for each Flipper, based on the data in the second and
third columns. ....
For example, for the first flipper, I can enter the following in
D2 and get my result:

= (RAND()<C2) + (RAND()<C2) + (RAND()<C2) + (RAND()<C2) + (RAND()<C2)

....

No VBA necessary. You have different binomial distributions. While Excel
doesn't have inverse functions for discrete distributions, such inverses are
just lookups.

=LOOKUP(RAND(),BINOMDIST(ROW(INDIRECT("1:"&(B2+1)))-1,B2,C2,1)
-(1-C2)^B2,ROW(INDIRECT("1:"&(B2+1)))-1)

 

[igor eduardo küpfer]

In microsoft.public.excel.worksheet.functions on Mon, 12 Sep 2005 22:49:42

...
...

No VBA necessary. You have different binomial distributions. While Excel
doesn't have inverse functions for discrete distributions, such inverses are
just lookups.

=LOOKUP(RAND(),BINOMDIST(ROW(INDIRECT("1:"&(B2+1)))-1,B2,C2,1)
-(1-C2)^B2,ROW(INDIRECT("1:"&(B2+1)))-1)

Very nice. Faster than the VBA. Thanks.
--

all the best,
ed

Epitome:
Nice kid, but about as sharp as a sack of wet mice.
Email:
edkupfer. It's a gmail addy.