Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neil Goldwasser
 
Posts: n/a
Default Column picked randomly with probability relative to number of entr

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which Id like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, Id like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? Id be
really grateful for any help.
Many thanks in advance, Neil Goldwasser
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Column picked randomly with probability relative to number of entr

First, John Walkenbach has a very nice Hangman game at:
http://www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
iCtr = iCtr + 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox "no words!"
End If

End Sub


Neil Goldwasser wrote:

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which Id like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, Id like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? Id be
really grateful for any help.
Many thanks in advance, Neil Goldwasser


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neil Goldwasser
 
Posts: n/a
Default Column picked randomly with probability relative to number of

Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
one that I have been adapting! (with his permission) Because I work in
Learnng Support, I needed to alter the wordbank a bit to suit the level of my
students, and this involved adapting the code to allow for 5-letter words, as
well as some particular spellings that are 13-letters long etc...

John very kindly left the code open so I have been able to edit parts of it,
and it functions just fine, but I haven't been able to rejig the random
choosing of the column. In John's version, all the columns have 200 entries,
so each word has an exactly equal probability of coming up, but the wordbank
I intend to use would not be uniform. That's why I'd tried to weight the
random process, but I'm at a loss for ideas on how to.

Thank you very much for the code though. I apologise for not forgetting to
mention that it was John's version that I'm adapting, but thank you for your
help.

Any ideas on how to weight the probabilities?
Cheers, Neil



"Dave Peterson" wrote:

First, John Walkenbach has a very nice Hangman game at:
http://www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
iCtr = iCtr + 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox "no words!"
End If

End Sub


Neil Goldwasser wrote:

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which I€„¢d like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, I€„¢d like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? I€„¢d be
really grateful for any help.
Many thanks in advance, Neil Goldwasser


--

Dave Peterson

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default Column picked randomly with probability relative to number of

Only the one I suggested--and that weights each word the same. But that seems
equivalent to me.

Neil Goldwasser wrote:

Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
one that I have been adapting! (with his permission) Because I work in
Learnng Support, I needed to alter the wordbank a bit to suit the level of my
students, and this involved adapting the code to allow for 5-letter words, as
well as some particular spellings that are 13-letters long etc...

John very kindly left the code open so I have been able to edit parts of it,
and it functions just fine, but I haven't been able to rejig the random
choosing of the column. In John's version, all the columns have 200 entries,
so each word has an exactly equal probability of coming up, but the wordbank
I intend to use would not be uniform. That's why I'd tried to weight the
random process, but I'm at a loss for ideas on how to.

Thank you very much for the code though. I apologise for not forgetting to
mention that it was John's version that I'm adapting, but thank you for your
help.

Any ideas on how to weight the probabilities?
Cheers, Neil

"Dave Peterson" wrote:

First, John Walkenbach has a very nice Hangman game at:
http://www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
iCtr = iCtr + 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox "no words!"
End If

End Sub


Neil Goldwasser wrote:

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which I€„¢d like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, I€„¢d like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? I€„¢d be
really grateful for any help.
Many thanks in advance, Neil Goldwasser


--

Dave Peterson


--

Dave Peterson
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neil Goldwasser
 
Posts: n/a
Default Column picked randomly with probability relative to number of

Firstly, my apologies for absent-mindedly forgetting to respond to this very
helpful post Dave. I posted the initial question during a particularly busy
period at work, and I'm afraid it got swallowed up with all the other things
I was working on - I'm very sorry!

For other people that may be browsing for similar solutions, there is also
another useful procedure code for forming one big array on the following post:
"Combine several columns of different length into one single column"

Many thanks again for your help Dave, Neil



"Dave Peterson" wrote:

Only the one I suggested--and that weights each word the same. But that seems
equivalent to me.

Neil Goldwasser wrote:

Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
one that I have been adapting! (with his permission) Because I work in
Learnng Support, I needed to alter the wordbank a bit to suit the level of my
students, and this involved adapting the code to allow for 5-letter words, as
well as some particular spellings that are 13-letters long etc...

John very kindly left the code open so I have been able to edit parts of it,
and it functions just fine, but I haven't been able to rejig the random
choosing of the column. In John's version, all the columns have 200 entries,
so each word has an exactly equal probability of coming up, but the wordbank
I intend to use would not be uniform. That's why I'd tried to weight the
random process, but I'm at a loss for ideas on how to.

Thank you very much for the code though. I apologise for not forgetting to
mention that it was John's version that I'm adapting, but thank you for your
help.

Any ideas on how to weight the probabilities?
Cheers, Neil

"Dave Peterson" wrote:

First, John Walkenbach has a very nice Hangman game at:
http://www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
iCtr = iCtr + 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox "no words!"
End If

End Sub


Neil Goldwasser wrote:

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc€¦ The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which I€„¢d like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, I€„¢d like column A to be picked at random 10% of the time,
column B 20% etc€¦

Does anybody know how to weight the RAND function in this way please? I€„¢d be
really grateful for any help.
Many thanks in advance, Neil Goldwasser

--

Dave Peterson


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SEeing if a number in one column is in another column Ricki Miles Excel Discussion (Misc queries) 4 February 17th 06 12:04 AM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Multiply two columns in Excel or one column by one number Axia's Mom New Users to Excel 1 January 12th 06 04:22 AM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 06:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"