Random Selection Of Cell Value From List Based On Changing Weighting
Apr 24, 2009
I have a list similar to the below:
#....|Name.... | Weighting %
1Artur Boruc 1
2Igor Tudor 5
3Alessandro Grandoni 3
4Gian Elia Amoretti 3
5Olivier Kapo 12
6Kim Källström 13
7Emiliano Bigica 13
8Julio Baldivieso 12
9Fabrizio Miccoli 13
10Peter Vougt 14
11Massimiliano Esposito 12
What I want to do is randomly select either number 1 to 11 (leftmost column OR the person it represents) but also make the selection based on the percentages in the rightmost column. To illustrate using the above example, Number 11 (Massimiliano Esposito) should have 12 times more chance of being randomly selected than Number 1 (Artur Boruc)...
I have used a formula such as
PHP
=INDEX(A$1:A$11,INT((RAND()*11)+1),1)
to randomly select a cell but how would I modify this to include cell weightings? Or is there a better formula?
An additional note I have is that the weighting for each person will always be changing, but will always be a percentage.
View 11 Replies
ADVERTISEMENT
Dec 8, 2008
I have column E listing all of the favorites and column G the underdogs, each row represents one game, there are 34 all together. I want a formula to pick the team based on a random generation. That part i have....
=choose(randbetween(1,2),e7,g7)
but here is the rub.
I only want the total number of underdogs to be 20% and the favorites to be 80% of all of the picks.
I am not sure it can be done as each formula must look to what the other formulas are doing...aside from it being circular i think it just impossible or i am not as advance as some of you.
View 14 Replies
View Related
May 15, 2013
I have been able to put together the below which generates a random 15% sample of all records in the worksheet.
It works perfectly, however what I would ideally like to do is get it to only produce the 15% sample of rows where it is todays date in col B and has the word "other" in col C. I've tried lots of different IF statements and FOR statements, but am only generating blank worksheets.
Sub getrandomrows()
Dim lr As Long, lc As Integer, a As Variant, MyDate As Date
lr = Cells.Find("*", LookIn:=xlValues, after:=[a1], searchorder:=xlByRows, _
[Code].....
View 6 Replies
View Related
Jan 10, 2007
Create a random selection based on odds. It would be similar to the RANDBETWEEN function, but in this case I specifically want one of the three values to be selected randomly in cell C2 based on the odds given.
View 9 Replies
View Related
Jun 24, 2008
I am working on a challenging project. I have a list of names, job titles, and departments. Is it possible to select a random name who matches a specified job title or department? I'd prefer not to sort/filter/delete the original data range because the sheet will be used multiple times during the day by multiple users and with different criteria each time.
View 6 Replies
View Related
Jul 23, 2009
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
View 2 Replies
View Related
Jul 2, 2014
I need VBA code to do the following:
When cell H2 = "Weekly", I need cell I2 to return "50".
When cell H2 = "Bi-weekly", I need cell I2 to return "25".
The value in Column I should change as soon as the value in Column H changes.
NOTE: The value in Column H is chosen from a Data Validation List.
I'm assuming offset is the best option?
View 7 Replies
View Related
Mar 7, 2013
[URL]....When I select a name in column B, it populates column C's dropdown with that name's list of units. When I select a unity type in column C, I'd like it to automatically populate column E in the same row with the value associated with that unit. The values associated with a particular unit can be found to the right of the sheet. So if in row 2 I select Blake in column B, then Unit 5 in column C, I'd like column E to be populated with 14.
Edit: using Excel 2007.
I attached stripped down version of the sheet which is small enough to post. None of the things I removed should affect the topic at hand. The full sheet is available at rapidshare.
View 6 Replies
View Related
Mar 18, 2014
I am trying to create individualized worksheets based off a master worksheet. I have different committee names that I want to appear on the individualized sheets, based on whether the person is apart of that committee or not. On my master sheet, I choose the persons name under the committee and I want that committee name to show on the individualized sheet. For example, I want A1 on Master to show on Name 1, B3 and A10 on Master to show on Name 1, C3. Can I write a formula to achieve this or or am I asking too much? In my actual file, I will have about 10 names and 5 committees with one person being on up to all 5 of the committees.
View 1 Replies
View Related
Feb 12, 2014
I am trying to develop a worksheet that will adjust dates based on a selection from a drop down box. I can do basic formulas in excel
I am trying to get the drop down box in "Revisit Type" to include three selections ("Sanitation", "Spark", and "High Five"). Once the dropdown box is created I want to be able to make a selection and enter an initial visit date under "Initial Visit Date" then have the "Earliest Return Visit Date" and the "Latest Return Visit Date" automatically populated based on this information below:
Sanitation or Spark = 10-30 day window
High Five = 10-21 day window
I am at a loss . I have attached a sample of the workbook for reference.
View 3 Replies
View Related
Jan 28, 2013
Changing the data based on a drop down selection. My drop down list is based on the months I need the data (percentages for each person) to change depending on which month is chosen. So If I select Jan, the percentage fills in for each person. I've attached an example.
View 3 Replies
View Related
Dec 15, 2009
Attached is the file i am working with. The table spanning from A:AA5:16 contains hours worked per day of the week with respect to the Project chosen from the drop down menus in A7:16. Cells in the range of F,K,O,R,U,X,AA7:16 total those hours worked on a daily basis. The table below should sum the total hours worked per day according to the Project selected in A19 from the drop down menu, with respect to the Project(s) chosen in cell range A7:16. For example, if i am looking for total hours worked Monday on Project RSB i select "RSB" from the drop down menu in cell A19, i want cell F18:19 to be the sum of cells A7 and A9, with respect to the Project selected from cells A7:16. If I select "FRG" from the drop down in cell A19, i want cell F18:19 to reflect the sum of cells F8 and F10.
View 2 Replies
View Related
Jun 24, 2009
I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.
The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0
i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.
View 8 Replies
View Related
Dec 28, 2011
I have 11 months of sales commission data, and need to estimate the value for December. However, the catch is, for the first 7 months, the values are significantly higher than the most recent 4 months. I'm currently using the TREND function to guesstimate the December value, but with the wide fluctuation between the 2 time periods, I'm thinking the result of the TREND value may be way off.
Is there a way to 'weight' the data to reflect the higher values earlier in the year, with the much lower values later in the year?
View 5 Replies
View Related
Sep 16, 2009
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
View 2 Replies
View Related
Dec 18, 2008
I am working on a spreadsheet that is essentially a question and answer based document .... I need to set up the macros so that if a certain response is chosen from a list, it will automatically update the cell / row next to it with a pre-determined response ....
The way the spreadsheet is set up is that the first column has the question, the second column has the answer (yes, no or N/A - from data validation - as a list) and the third column is blank - but is titled "comment/action" .....
For example: if the question is "have you locked the car" and the answer column selects "NO" then I want the "comments/action" to come back with "you need to lock the car", and if the answer is "YES" then I want the comment/actions column to remain blank ...... The same with N/A ......
I will have around 100 questions in the document ...... I know I need to set up the responses on a separate worksheet - i just dont know what the macro's are to identify and place the appropriate response from the worksheet to the relevant comment/action column .....
View 10 Replies
View Related
Jan 15, 2012
I have to filter a data based upon the selection list value.
Ex:
Assume that i have 4 values listed in my SELECTION LIST
INDIA
US
UK
AUS
If i select "US" in selection list ...Filter has to take this dynamically and filter the data..
View 2 Replies
View Related
Oct 23, 2007
I have 2 Fields (Columns) and I have implemented Validation Lists in both these 2 columns.
I have around 10 values in Column A which are available in the List and have around 50 values in Column B in the List.
In other words, one of the five values or items can be selected for a single and unique value in Column A.
is it possible to call one of these 5 values in Column B depending upon the selection of the value in Column A ? Both the columns have validation lists.
View 9 Replies
View Related
Dec 4, 2007
how to get a 2nd drop down box to update the range (Not just use C6:C7)based on selection in 1st drop down box.
Example:
If Fat, Use range from C6:C7
If Skinny, Use range from D6:D7
Note: I tried putting an if statement in the input range for box2 but won't accept it.
View 9 Replies
View Related
Jun 8, 2007
I have created a pricing sensitivity for a list of products. In cell C3, users can select a product (i.e. Apple, Elmo, or Bowl) and based on this selection, a range of Prices and Units are then updated and the Revenue for each price point calculated. A few lines beneath this, I have a little summary table that lists all the products. I'd like for users to be able to enter in the optimal price point for each item and then have the Units and Revenue for that selected price point for that specific product automatically update.
For example, if I select Apple from my drop down list my price choices are $5, $10, $15; units are 100, 85, 70; and revenue are $500, $850, and $1,050 respectively. On the summary table, I would like units 85, revenue $850 to update automatically if I select/input a $10 price. This would happen for all the products so at the end I want to be able to calculate a blended revenue mix given my pricing changes.
View 9 Replies
View Related
May 7, 2008
I have been searching through the site trying to find a solution to my issue, but I just can't seen to fixt it. Here is the scenario. I am working on a spreadsheet that will read some data, use that data to update the main chart and fill in the information in a table next to that chart. I recoreded 22 macros, total. Each macro corresponds to the especific type of chart that I need/want.
I want to be able to click on a drop down menu above the chart which will have each of the 22 options. Once the user clicks on the desired option, the specific macro will run and do that it should do. When I run the individual macro (without the VB that I wrote and will mention below), the system does what it should do. Everything looks dandy. But then I added this VB code to the sheet where the chart is:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Range("A12").Value
Case "1"
Call a
Case "2"
Call b
Case "3"
Call cc
Case "4"
Call d
Case "5"
Call e................
View 2 Replies
View Related
Nov 21, 2012
I am new to VB macros, I am trying to figure out how to color a tab Green based on a selection from a drop down list.
View 9 Replies
View Related
Jun 14, 2014
I need to make a dropdown list (I know how to do that) and based on that selection, I need some cells below to automatically complete. How can I do that? Is it possible with or without VBA?
View 5 Replies
View Related
Jan 13, 2009
I have data for number of firms as :
year firm
1 1
2 1
3 1
1 2
2 2
3 2
1 3
2 3
3 3
.............
.............
1 1000
2 1000
3 1000
how can one chose random samlpe from this population.
View 3 Replies
View Related
Jun 28, 2009
I have a file (see SAMPLE file attached) with over 2000 records. I need to know if there is a way to do a sampling or random selection by ethnicity and gender in order to get a diverse population for only 60 individuals. The selection criteria should be on ethinicity and gender.
Is this possible?
View 10 Replies
View Related
Apr 19, 2006
random number selection...
i have a list of 200 items (rows) and i would like to "randomly" select 30 of them...(next month i will get another 200...)
View 3 Replies
View Related
Dec 13, 2009
I'm using Excel 2007. I would like to seek some advise on how i may update cells after selecting a month from a drop down list.
i have created a simplified version of what i intend to have. One the 1st tab 'Cash Budget 2009', i have filled up a table with numbers, sorted by months. On the 2nd tab 'Dec09', i would like to create an expense table, to be able to be selected by month. So i have created a drop down list based on the months that are created in the 1st tab.
Upon selection of the month from the drop down list, i would like the cells to display (fetch) the data from the 1st worksheet. It would also be helpful if the name of the 2nd tab can be updated to read as the month that is being selected.
View 4 Replies
View Related
Dec 26, 2012
I've created seven names in my data validation source list on Sheet 3 in the following range of cells, A2-A7. My data drop down validation list is located on Sheet 1, A2 in the same workbook. I would like to trigger a macro based on the name I select in this drop validation drop down list that will fill out an address, telephone number, and email address in the following cells next to my data validation drop down list on Sheet 1: B2, B3, and B4. I want it to be designed so that users will select a name and have that person's corresponding address, telephone number, and email address fill right next to their name on the same sheet.
View 6 Replies
View Related
Jun 27, 2014
I would like to be able to limit a drop down list's contents based on a user's selection in a different drop down. I am using Excel 2003, and I know how to do this in Access, but I can't figure it out in Excel. Basically, I have a list of themes in one drop down. The user picks a theme, and goes to a second drop down which is a list of sub-themes attributable to that one theme and no other theme.
View 1 Replies
View Related
Feb 8, 2009
I'm trying to select an x number of random selections within the selection.
Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them
This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?
View 9 Replies
View Related
