weighted random numbers

Would appreciated any help on this.  I'm looking to create a
spreadsheet that can generate weighted random numbers much like
professional sports leagues do with their draft lotteries.  I play in
a league with 20 teams and we have a draft every year.  I would like
to generate a lottery where the teams that finished near the bottom
would have a greater ( say i.e 25%) chance of selecting the 1st pick
than the teams that finished near the top.  Any input would be
appreciated.  Thx

Jorge
0
4/14/2007 9:23:49 PM
excel 39879 articles. 2 followers. Follow

3 Replies
882 Views

Similar Articles

[PageSpeed] 8

Jorge <eisen_h...@yahoo.com> wrote...
>Would appreciated any help on this.  I'm looking to create a
>spreadsheet that can generate weighted random numbers much like
>professional sports leagues do with their draft lotteries. I play
>in a league with 20 teams and we have a draft every year. I would
>like to generate a lottery where the teams that finished near the
>bottom would have a greater ( say i.e 25%) chance of selecting
>the 1st pick than the teams that finished near the top. Any input
>would be appreciated.  Thx

If you have team standings in A2:A21 (first to last place), team names
in B2:B21, then enter, say, 0.75 in C2:C5 (top 4 teams have 75% of
average odds), 0.90 in C6:C9 (next 4 have 90% of average odds), 1 in
C10:C13 (middle 4 teams have average odds), 1.1 in C14:C17 (next 4
have 110% of average odds), and 1.25 in C18:C21 (last 4 teams have
125% of average odds). Enter =SUM(C2:C21) in C23. Enter 0 in D2,
=D2+C2/C$23 in D3, fill D3 down into D4:D21. Then use the formula

=LOOKUP(RAND(),D$2:D$21,B$2:B$21)

to give the team getting the top draft choice. Change the weights to
any monotonically increasing positive values in C2:C21 to change the
odds of getting the top draft choice.

0
hrlngrv1 (375)
4/14/2007 11:03:05 PM
Thanks for all your help.  Really appreciate it.

Jorge


On 14 Apr 2007 16:03:05 -0700, "Harlan Grove" <hrlngrv@gmail.com>
wrote:

>Jorge <eisen_h...@yahoo.com> wrote...
>>Would appreciated any help on this.  I'm looking to create a
>>spreadsheet that can generate weighted random numbers much like
>>professional sports leagues do with their draft lotteries. I play
>>in a league with 20 teams and we have a draft every year. I would
>>like to generate a lottery where the teams that finished near the
>>bottom would have a greater ( say i.e 25%) chance of selecting
>>the 1st pick than the teams that finished near the top. Any input
>>would be appreciated.  Thx
>
>If you have team standings in A2:A21 (first to last place), team names
>in B2:B21, then enter, say, 0.75 in C2:C5 (top 4 teams have 75% of
>average odds), 0.90 in C6:C9 (next 4 have 90% of average odds), 1 in
>C10:C13 (middle 4 teams have average odds), 1.1 in C14:C17 (next 4
>have 110% of average odds), and 1.25 in C18:C21 (last 4 teams have
>125% of average odds). Enter =SUM(C2:C21) in C23. Enter 0 in D2,
>=D2+C2/C$23 in D3, fill D3 down into D4:D21. Then use the formula
>
>=LOOKUP(RAND(),D$2:D$21,B$2:B$21)
>
>to give the team getting the top draft choice. Change the weights to
>any monotonically increasing positive values in C2:C21 to change the
>odds of getting the top draft choice.
0
4/15/2007 2:34:26 AM
Hi Jorge,

I suggest to use my UDF REDW:
http://www.sulprobil.com/html/redw.html

Regards,
Bernd

0
bplumhoff1 (208)
4/15/2007 6:28:12 PM
Reply:

Similar Artilces:

Seemingly random plot area drift?
I've got a really frustrating issue with Excel charts. I am using Excel 2003 SP3 and XP. I have a large workbook with many tabs. One of the tabs has about a dozen bar charts. Every time I copy these charts into Word, and then go back to Excel, the plot area has randomly moved. I don't do ANYTHING in this tab except copy and paste those graphs. There is no shifting of column widths etc. Specifically, the plot area seems to shrink. I make a quarterly report with these graphs and each quarter the plot area has shrunk until it gets so small and scrunched into the midd...

Convert Time Into A Number To Multiply By A Currency.
This is what I am trying to do: creating a spreadsheet to forcast a employee work schedule, I am inputting the start time and finish time totalling each position and totalling all the hours, so far so good Now what I want to do is convert the total time into a number, done only problem is the number only works as long as the hours do no exceed 24 so how do I create a formula which will recognize the numbe under 24 hours as is and recognized the number above 24? example: for the total of 109:10 hours I am using the formul =HOUR(B1)+(MINUTE(B1)/60) but I get the total 13.17 I am looking fo the...

Fill in Column with a series of repeating numbers
I am trying to fill in an amortization years column. The first 12 rows are "1", the next 12 rows are "2", the next twelve rows are "3" and so on. I've got 360 rows to fill. Any suggestions? A1: 1 A2: =IF(MOD(ROW(),12)=1,A1+1,A1) Fill down -- Jim Cone Portland, Oregon USA ( Review of Special Sort add-in: http://www.contextures.com/excel-sort-addin.html ) "mimi" <mimi@discussions.microsoft.com> wrote in message I am trying to fill in an amortization years column. The first 12 rows are "1", the next 12 ro...

Find cells containing a specified number of characters
I have a spreadsheet with only 1 column of data, but 18,000 lines. Is there an easy way to find all cells that contain exactly 12 characters? I tried searching for ???????????? and ************ but it finds every cell. If you use Data->Filter->Advanced Filter you can use the 12 ?s as your filter. You need a column header - let's say it is Values, and let's say your data is in column A Then in C1 type Values and in C2 type ???????????? Then select a cell in column A and go to Advanced Filter. Set C1:C2 as the criteria range, and clikc on OK "jdanker" wrote:...

Random Time #2
I want to generate a randome 24 hr time for a given date. The time can be rounded to the nearest hour or half hour. I am trying to create a sheet that has a 1 year date column and a unique random time column for that date to manage an equipment testing schedule. Thanks for any help, Steve Just use the RAND() function and format as time. YOu'll probably want to convert the formulas to their values so they won't continually recalculate. Do that via Edit/Copy, then Edit/Paste Special and select the Values option. On Sat, 23 Oct 2004 09:49:02 -0700, SteveMcDowell19 <SteveMcDowe...

Sets of numbers
hi Need your help, one again. Have a set of numbers, that meets one specific condition (acosh or other formula). Need to compare with other sets, and if equal, need to extract the set which is on the following row. Can anyone help, is there any formula(s) Tks in adv Antonio Hi could you post some example data 8plain text - no attachments please) -- Regards Frank Kabel Frankfurt, Germany "Antonio" <Antonio@discussions.microsoft.com> schrieb im Newsbeitrag news:3F5B91A9-D0C1-44F4-B7C9-A9FB71A4236B@microsoft.com... > hi > > Need your help, one again. > > Hav...

Weight Weight Weight
So if I want to give a weighted score/percentage.....how would I? For example, PEOPLE + REQUIRED OBSERVATIONS + WEIGHTED SCORE BASED ON TYPE OF SCORE = OVERALL SCORE/RANKING So.... If I have 3 types of observations.... If Type A = Hardest Type Type B = Average Type Type C = Easiest Type How do I weigh the QUALITY of their production? So...... 10 people x 6 observations each + Type B observations = ......... Hi! Only you can know how you want to weight them so you will have to b able to define in a suitably mathematical way how scores will b amended to recognise this weighting and by ...

today's day number
Hello, I just want to know the function (if any) that returns the day number of today, for example, if today is Monday I want to get 1 and for Sunday 7 (or what any default for excel) I dont care for the value of the number but I want a number between 1 and 7. any help would be highly appreciated thank you Hi =WEEKDAY(A1) or =WEEKDAY(A1,1) returns the number of weekday with Sunday=1 ... Saturday=7 =WEEKDAY(A1,2) returns the number of weekday with Monday=1 ... Sunday=7 =WEEKDAY(A1,3) returns the number of weekday with Monday=0 ... Sunday=6 -- When sending mail, use address arvil<at&g...

Counting the number of rows between identical data.
Hello, I have a spreadsheet that lists the number of F1 races and the drivers who won those races. I'm trying to count the races that elapsed between wins. Column B contains the race #: (from 1 through 754) Column L has the race winners. In Column M, I want to count how many races it's been since a driver last won a race. For example: Giancarlo Fisichella won his 2nd race at the 2005 Australian G. P. (Race # 732) He won again at the 2006 Malaysian G. P., (Race # 753), 20 races had elapsed. Is there a formula I can use in Column M that will display 20? Thank you for you...

whole Numbers entered; percentages displayed.
I want to create a chart from data similar to the following example: x1 x2 x3 total 1000 500 750 a 250 100 375 b 300 200 100 c 200 50 180 On the chart, I want the Y-axis to show the amount but the data label inside the chart to show the percentage each number is of the total (the top number) in its column. Possible? Difficult? Thanks to anyone who can/will help! Make some columns with calculated percents then use a chart label addin. Rob Bovey's Chart Labeller, http://appspro.com John Wa...

How to apply a weighting
I'm trying to apply a weighting to my teams to allow for experience and size I have 3 teams which are different sizes and are different skill levels. Level 5 is the lowest skill level and level 1 being the highest skill level. Team size/level5/level4/level3/level2/level1 6/3/2/0/1/0 10/2/3/4/0/1 7/0/2/3/1/1 How do I apply a weighting to these numbers to allow for team size and experience. Can I do this using sumproduct? I tried looking at this but cant get my fragile little mind around it Thanks in advance. -- Homer J ---------------------------------------------------------------...

weighting a value?
Let's say I have 10 hours to accomplish 10 tasks. Having previousl accomplished each of these tasks I know some will take longer tha others. Let's say I want to weight them into three categories, easiest receives a weight of 1, harder 1.5 and harder yet 2.5. Wha Excel trick, or someones know how, would be used to spread my 10 hour appropriately over the 10 tasks -- jvan10 ----------------------------------------------------------------------- jvan100's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2922 View this thread: http://www.excelforum.com/showt...

negative numbers in bar charts
Whenever my data contains negative numbers, the titles appear at the "0" line while the bars extend below, through the titles making them difficult to read. How do I set the bottom of the chart so the titles appear at the bottom instead of at the "0" line? Right-click on the vertical axis>select Format Axis Select Scale tab Change the "0" under "Category (X) axis Crosses at:" box to a value lower than the highest negative value, say: -50 (if the highest negative is -10) Click OK. Optional: Format the plot area colour to None for a clearer look W...

Problem entering the number 9 in a cell
For some reason, and I'd be very interested to know WHAT the reason is, I cannot select a cell and enter the number 9. If I double-click on the cell I can enter the number 9, but not just by selecting the cell. If I just select a cell and press 9 the system beeps at me. What could this be? -gk- Look at the Autocorrect entries. Bernard "TBA" <toxicdistortion@hotmail.com> wrote in message news:vlrjncc9f5f2b6@corp.supernews.com... > For some reason, and I'd be very interested to know WHAT the reason is, I > cannot select a cell and enter the number 9. If I ...

the letters across the top in excel have changed to numbers why
how can i fix this Tools | Options | General tab | switch off R1C1 reference style Regards Trevor "popabear" <popabear@discussions.microsoft.com> wrote in message news:820E7407-7AC8-4FD3-8D3B-F5160B5441D7@microsoft.com... > how can i fix this ...

Creating a large number of charts automatically
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have to code a solution to this it'll probably be done in ASP on a web server, unless there's a significantly better way. I'm looking for a way to create over 100 Excel *charts* programmatically. We've just run a large online survey and the data is being fed back to us in a single spreadsheet - each respondent on a row and their dimensions (division, department, etc) and each of their...

Row Numbers
Hi, If I delete a row in excel i.e. row 7 then the row numbers go out of sync i.e. 5 6 8 9 etc is it possible to correct this? Thanks for your help Shaun, It seems as if row 7 is HIDDEN, not deleted. To unhide row 7, do this: 1. Select cell A7 by pressing the F5 key, type A7 and press Enter. 2. Format - Row - Unhide 3. If row 7 still doesn't show, do this: Format - Row - Autofit (To delete a row, if that is what you want, select a cell in the row, then do Edit - Delete - Entire Row - OK.) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "Shaun" <shaun@man...

tracking the active cell row number
I posted this earlier and got a way of doing it by using CELL("row") an pressing F9 each time I change row, but that seems a bit cumbersome. What I would like is someway of either a formula or macro running i the background (maybe on each up or down arrow or enter key stroke constantly checking the active cell and displaying its row number i another cell. I am trying to create a data entry table and the colum headers chang on the result of the first entry in each row. I can get this to work by adding an 'add data line' button which check the content of the first colum and...

Weighted items
Does anyone know of a way to weigh an item on a stand alone scale, print out the price and weight on the barcode, and then scan it at the register without having to weigh the item again. Can RMS read the barcode and understand the item, price, weight and in turn calculate a total? Thanks! Use Random Weight UPC. Search this newsgroup for more info. Regards Michael "zoostation" <zoostation@discussions.microsoft.com> wrote in message news:0D7EF298-BCA1-474F-B3FA-D96E53E26F91@microsoft.com... > Does anyone know of a way to weigh an item on a stand alone scale, print ou...

printing row numbers in Excel 97
Hi again, along with the gridline challenge, I can't seem to find a way to print the row numbers.. is there a way to do that. this document has 11 pages horizontally, and total pages for printing is 220, having row numbers would be VERY helpful. Any way to do that easily? thank again, gary File|page setup|sheet tab check that box for "row and column headings" If you really only want the row numbers, but not the column headings, you'll have to do it yourself. A helper column (new column A) filled with formulas: =row() niteowl wrote: > > Hi again, > >...

Help ASAP. Changing number string into date?
I pull reports from an internet site into excel for state reporting purposes and need to calculate the duration from one date to another. The problem is when I pull the table in it chages the dates to a string (i.e. 06/02/2001 looks like 6022001, and 12/02/2001 looks like 12022001). Is there a formula that will take the 6022001 and 12022001 and chane them into date format? I tried the format tab and it wouldn't work. TIA John, You need to include an example date that shows us where the day/month appear. I believe all US dates are m/d/y, but UK, Australian, etc. dates are d/m/y. Use a...

Random color changes
Does anyone know why a presentation (2007 saved in compatibilty mode) would suddenly begin to change line colors on shapes and in charts within the presentation. There are no links to anything outside of the presentation, the file is not shared. I opened it to make my monthly updates and the colors were changed from what I have had there for months. No one else has opened the file. Thanks -- bert_lady Did you select a new color scheme or template/theme on the Design tab? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.c...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

generating random numbers
I want to pull a group of random numbers within a certain range, but don't want to check after each grab whether I've previously pulled the number. For example, let's say I want to generate three random numbers between 1 and 100. I'll store the numbers in an array. Can I do this without having to search through the array each time I generate a new number to ensure I haven't already pulled the number? Thanks in advance. I would suggest the following code to generate a list of random numbers. The function is set up to return the random values in two ways. It will...

Converting strings to numbers
I want to join the values of a range of cells (say 1, 2, 5, and 8) to produce the number 1258, which can then be used in other calculations. Any ideas on how to do this? I'm using Excel from Office 2000 Many thanks Formula =--(C9&D9&E9&F9) VBA code varData = --(Range("C9") & Range("D9") & Range("E9") & Range("F9")) -- Jacob (MVP - Excel) "Big Bitty" wrote: > I want to join the values of a range of cells (say 1, 2, 5, and 8) to produce > the number 1258, which can then be used i...