Rand Pick From 4 Pools

Mar 6, 2009

I have been using the RAND and Vlookup functions for the following without success.

I have 4 numeric Pools or Groups;
1. A1:A18 (1-18)
2. B1:B18 (19-36)
3. C1:C17 (37-53)
4. D1:D17 (54-70)

I also have E1:E20 (20 existing Numbers from 1-70)

I want Excel to select at random a total of 20 numbers
and place them in F1:F20.

I will specify a quantity from each of the 4 groups.
Eg.
In cell A20 I enter 4 to obtain 4 numbers from grp 1,
In cell B20 I enter 6 to obtain 6 numbers from grp 2,
In cell C20 I enter 8 to obtain 8 numbers from grp 3,
In cell D20 I enter 2 to obtain 4 numbers from grp 4,

However there must not be any duplicates between
E1:E20 and F1:F20!

View 9 Replies


ADVERTISEMENT

RAND BETWEEN

Nov 12, 2009

I need to use a function that is not onyl random, but only uses a person's name on a list once. On a list of ten people, RANDBETWEEN picked the same person three times in ten key strikes. How can I use RANDBETWEEN but also make sure ever person's name gets picked ONLY once? This is for a drawing for secret santa.

View 9 Replies View Related

Changing Values With = RAND()?

Dec 29, 2009

When I use the =RAND() function, everytime I do anything within my spreadsheet the number changes. Is there a way to get that number to change after(for example) 10 refreshes of my spreadsheet instead of every single time I do something to it?

View 6 Replies View Related

Calculate RAND Function Only Once

Nov 10, 2013

I have a workbook that calculates a RAND function based on input in another cell. Specifically, the formula in E2 selects a random text string from another range depending on data entered in D2. I want to be able to fill this formula down for use multiple times.

The formula works fine on its own, but the problem comes when I, say, move on to D3 to input more data. E3 does its random calculation as it should, but E2 also recalculates; I want E2 to remain static once it calculates the first time.

I'm attaching the workbook in question. The calculation I'm referring to is in the 3rd worksheet.

TLL Injury Machine.xlsx

View 2 Replies View Related

How To Use RAND To Generate Random Numbers Between 0.25 And 0.5

May 23, 2013

How do I use RAND to generate random numbers between 0.25 & 0.5?

View 9 Replies View Related

Fix Random Numbers Generated With RAND

Oct 9, 2007

Is there a way to control the number generation. I would like to be able to run different scenarios with the same set of random numbers, then regenerate the random numbers and do it again. Right now every time I do anything in the spreadsheet, I get a new set of numbers.

View 4 Replies View Related

Using RAND Function - Assign Number From 1-5 To Each Of Identity

Apr 29, 2014

I have 379 sample identities in my Excel spreadsheet in A2:A380. I want to randomly assign a number from 1-5 to each of these.

View 1 Replies View Related

RAND Function To Generate Phone Numbers

Oct 24, 2007

How can I use the RAND function to generate a list of simulated phone numbers.

View 9 Replies View Related

Excel 2010 :: How To Make BINGO Board Using RAND

Aug 25, 2013

I'm making a custom BINGO board in Excel 2010 for a family reunion, so it doesn't contain numbers; instead it has words (like "has brown eyes" or "served in the military"). the first sheet is the board and looks like a traditional BINGO board with the same number rows and columns with a center FREE space. On the second sheet is the list of all the phrases I'm going to use, one phrase per cell, all in Column A.

In BINGO cells, I want it to use one item (phrase) in each box but NOT repeat within the board. I have the following code in each cell and it works EXCEPT, there is no code telling it to make each cell unique, so sometimes it may repeat.

=UPPER(INDEX(ColumnA,((RAND()*(Data!A1-1))+1),0))

View 8 Replies View Related

Pick Up Some Value's From A Table

Oct 6, 2008

I have the next situation:.......

Qmin - is the first smallest value than Q (in this case Qmin=[B5]=0.63)
Qmax - is the first highest value than Q (in this case Qmax=[B6]=0.77)
Hmin - the value attached to Qmin (in this case Hmin=[A5]=0.004)
Hmax - the value attached to Qmax (in this case Hmax=[A6]=0.005)

question: how can i make Qmin,Qmax,Hmin,Hmax to pick up themselfs if I have a given Q=[B12] value????

View 4 Replies View Related

Pick Five Numbers

Mar 19, 2009

I have a worksheet that has all the numbers that I picked for the pick 5 lottery, listed from B24:N71. These are 5 different numbers. What I want to do is highlight the cells that equal the 5 numbers that were drawn, which I have located at S5,T5,U5,V5,W5

View 11 Replies View Related

Pick From List

Jul 17, 2007

I am managing a group of sales reps and when they sell a product they receive commissions I am trying to develop something in excel that in cell B5 there will be a drop down that they can choose one of three products (call them A,B,C) then in C5 another drop down will be there w/ two options (new, refurb), then the result will be in D5 depending on the selections. The parameters would be:

Product A new = $10
B new = $20
C new = $30
A refurb = $5
b refurb = $15
C refurb = $25

So if the rep dropped down in cell B5 Product B then dropped down in cell C5 Refurb, the result would appear in D5 and would equal $15.

View 9 Replies View Related

Using A Message Box To Pick Someone

Jun 24, 2006

Basically I want a button that once pressed generate a message box that says a persons name. There are 3 names, so the message box should show name 1 1st and then when pressed again show name 2 and you guessed it then name 3!

View 9 Replies View Related

Pick Value From By Count Criteria?

Apr 3, 2014

At the moment I have a problem as per example attached, that I need to select values from row and populate respective column based on the following.

1. A top row contains non-negative values

2. Rows below it contain blanks, negative values and positive value.

3. Last column to be populated with a value from the top row if the value in the rows below top row contains the last negative value before the first positive value. There could be blanks between the last negative and the first positive, but I want the last negative to be used as a criteria for selection of corresponding value from top row. I hope my question is clear with the attached example. The column to be populated is FY.

View 6 Replies View Related

Trying To Pick Result From A Table

Apr 7, 2014

In short in the attached which is just 5 results (the full workbook has a couple of data tables and if/or's etc., I've stripped all of the workings out for ease) from one of the sims in the workbook, it shows how far each of the 8 teams listed will get.

Basically in the top table (results which I have highlighted green), I am just trying to return the values in column J (highlighted yellow) which I have just inputted in the attached. Basically I then intend to use them as a results cell for a data table (for each team). As you can see from looking at the 5 sets of results in the case of St.Louis Blues it would return, STANLEY, DIVISIONAL, SEMI, WIN, WIN.

I wasn't sure quite how to do this. I thought I could possibly do this with an index and a match (but wasn't quite sure how to express the function).

Results_Test.xlsx

View 4 Replies View Related

How To Pick 50 Numbers Randomly

Dec 10, 2013

In sheet1 columnA I have some data from that i want to copy randomly 50 rows to sheet2.

View 14 Replies View Related

Formula To Pick Last Value In A Column

Mar 23, 2009

Is there a formula or function that allows me to always select the lowest (not min) actual lowest in a column? For example, I have a formula in A1 and I need it part of it to always reference the bottom of a certain range (i.e. A5.A200).

So one day the lowest would be A7 but a week later it could be A8. I don't want to have to keep changing the formula in A1 to find the lowest value. I will be adding data on a regular basis.

View 6 Replies View Related

Pick Nearest Data?

May 18, 2009

If I have a date that is 12 February 2008 I need to pick up data next to 31 January 2008 as that is nearest, however if my date is >15th February 2008 I want to pick up the data in the column next to 29 February 2008......

View 2 Replies View Related

Color-pick Eye Dropper

Feb 2, 2010

I am trying to create a front-page, control-panel style user form that among other things would enable the user to recolor certain aspects of the entire workbook based upon an eye-dropper tool that would pick any color from anywhere on screen (inside or outside of excel).
Ideally, I would like it to be a simple button, click on color, and then have a square next to the selection panel that would display this color. So an add-in would be the most viable solution, however, any form of coding that works is good enough for me.

I am currently at the stage of being able to enter a hex value into the form manually, display the color box using that hex-color in the form next to the selection panel, and upon completion of the form, edit which colors I need within the entire workbook.

And I have an external application that will select the hex value of any pixel's color and paste it in the clipboard, so if could figure a way to run the small application upon the button-click, take the value from the clipboard, and then close the application, that would work.

View 6 Replies View Related

Pick The Minimum Numbers

Oct 21, 2008

I need a function like a sumif, but rather than adding together numbers, I want it to pick the minimum. For example, if I had a list of prices for various products, I want the formula to go and find the lowest price for a particular product.

View 2 Replies View Related

SQL In VBA - Pick Record With Certain String In It

Dec 4, 2012

I'm trying to use VBA to pick records with a certain string in them using the following:

Code:
"SELECT * FROM tblIncidents WHERE tblIncidents.[Detail] LIKE %" & txtSearch.Value & "%"

But it's not working. It says it has a missing operator...

View 3 Replies View Related

Pick Up Certain Information Into New Sheet?

May 27, 2013

I have a table of data as below and would like a formula that will pick the number and name only (as in the need bit) - can this be done, they are on 2 seperate sheets.

DATA
Need
Number
Name

[Code]....

View 2 Replies View Related

Allow User To Pick Folder

Jun 5, 2006

My VBA code in Excel 2000 copies a worksheet and Saves it. The default path of the file location is held in a cell. The user defines this path through a userform. The userform simply has a text box and the user types in the path. This value is then transferred to the mentioned cell. What I want is for the user to be able to pick a directory from the user form rather than having to type it.

View 5 Replies View Related

Pick Name At Random Name From Table

Dec 13, 2007

I have copied and pasted the formula off the ozgrid page using a 3 column table laid out as I believe it should be however I just get the # name? error message what I am not doing right. Please help. I just want to be able to generate a random name from a table of names, eventually I need to amend the formula to encompass a larger table but I can't get the example to work!

This is the page I have got the info from
[url]

I have uploaded the example I am using.

View 3 Replies View Related

Pick Random Name From Column

Dec 24, 2007

I've got a list of names in a column in excel, what's the easiest formula to pick a random name from that list? (guess I can use f9 to randomize)

View 6 Replies View Related

Pick From Range At Random

Dec 30, 2007

We get several sets of season tickets to various events to be distributed amongst several managers based on the mgr's headcount. So mgr1 may get 20 dates while mgr2 gets 5. All this info is calculated on Sheet1, but the only info that is important for this exercise is the mgr's names and how many dates they get. Available Dates will always be the same as Total Headcount.

Short of writing names on sheets of paper and drawing names from a hat, I would like to automate this process w/ a push of a button.

Sheet1 - this is where the managers are listed and their Headcount is calculated.

B C
10| Mgr1 Mgr2
12| 20 5

Sheet2 - this is where my ticket information is listed and will change based on different events.

A. B. C. D.
1| Date Row Seat Mgr
2| 1/1/08 H. 1-4. Mgr1 (20 times)
3| 1/1/08. I. 1-4. Mgr2 (5 times)

Ideally I would like to place a button on Sheet1 or 2 that would execute a code that looks at the range of Mgr's names and based on their hdct, inserts their name THAT many times in column D of Sheet2 like the above example.

This information should not change once it's assigned or unless the button is pushed again. The number of Mgr's names in the row on Sheet1 will change, but is never be more than 15, so the code must skip over cells that are blank if referencing, for ex. B11:P11.

View 9 Replies View Related

Pick The Number Farthest Down In A Column

Mar 17, 2009

The attachment shows a ledger that I am trying to build. My goal is to automatically add the Ledger balance(G) at the end of each day to the Investment cash balance(J) column. Some days there are multiple Ledger transactions, other days there are none. If there were no Ledger transactions on that day I would like to use the last recorded Investment cash balance. (ex. on weekends there will be no transactions)

Note: I want exactly one balance, the ending daily balance, per day on the investment side of the worksheet.

I have struggled with this all afternoon. My goal is to make this as simplistic as possible for two reasons. 1) I am not a master programmer by any means. 2) I want this file to open on any computer the has a MSExcel on it without have to install any addins.

For these reasons I am thinking it would be best to stay away from macros, which I am minimally proficient at creating.

Let me know if you have any good tricks to solve my problem. If macros are the ONLY or absolutely the BEST way to do this, then I guess you can twist my arm an we can go down that avenue.

View 14 Replies View Related

Drop Down Menu That Allows To Pick Any Team

Apr 11, 2009

I’m trying to do now is I have a drop down menu that allows me to pick any team that we can face in the season. Once that is chosen what I want to show is the individual stats against that team. Now right now I’m just trying to work out the formula to see if it works.

The formula itself is half working. For some reason some of the selections aren’t showing up like there suppose to do.

View 10 Replies View Related

Pick Up Line Items By The Value In One Column

Feb 12, 2014

As per attached file, there are altogether 4 sub-files and 1 master file.

I'll need the code to automatically pick up the top 9 revenue(highlighted in Green) and top 9 EBIT(highlighted in Yellow) by values in Column N from 4 sub-files and paste to the master file.

Attached File : Example file1.xlsx‎

View 9 Replies View Related

Pick Values From One Column And Organize In Another

Mar 31, 2009

I have a column of numbers and I want to copy numbers, with a constant periodicity, and paste in a new column. In the example below, I copy values from column A and past in column B, picking only every third value:

A B
1 1
2 4
3 7
4 10
5
6
7
8
9
10

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved