Randomly Choose Cells from Colum/Row

hello. i'm not sure if this is possible, but i'm trying to randomly select 
multiple cells from a colum to be evaluated. for example, say i have a clumn 
of 10 cells. i'd like this function in another cell to select 3 cells from 
the 10 at random. is this possible? thanks
0
J (145)
11/18/2005 7:09:03 AM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
577 Views

Similar Articles

[PageSpeed] 55

In a column next to the column containing the ten data items enter:

=RAND() and copy down.  Touch F9 and then sort by the newly entered column.  
This will "randomize" your data.  Just take the top three cells.

Whenever you want a new sample, repeat the process
-- 
Gary''s Student


"J" wrote:

> hello. i'm not sure if this is possible, but i'm trying to randomly select 
> multiple cells from a colum to be evaluated. for example, say i have a clumn 
> of 10 cells. i'd like this function in another cell to select 3 cells from 
> the 10 at random. is this possible? thanks
0
GarysStudent (1572)
11/18/2005 4:20:28 PM
Another variation to try ..

Assume the 10 source items are listed in A1:A10

Put in B1: =RAND()
Copy B1 down to B10

Put in C1: =INDEX(A:A,RANK(B1,$B$1:$B$10))
Copy C1 down to C3

C1:C3 will return a random, non-repeating selection
of 3 items from within A1:A10

And if you want to random select more than 3 items,
just copy C1 down further to C4, C5, .. or all the way to C10
to get the random full scramble of what's in A1:A10

To regenerate a fresh random selection, just press F9
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"J" <J@discussions.microsoft.com> wrote in message
news:C02D96E4-9408-466E-B267-C54316D9FBD7@microsoft.com...
> hello. i'm not sure if this is possible, but i'm trying to randomly select
> multiple cells from a colum to be evaluated. for example, say i have a
clumn
> of 10 cells. i'd like this function in another cell to select 3 cells from
> the 10 at random. is this possible? thanks


0
demechanik (4694)
11/19/2005 2:22:57 AM
Reply:

Similar Artilces:

Find out, if test doesn't fit in a cell
Hi, I'm automatically inserting text in Excel cells. The cells height and width is fixed and shouldn't be changed. I'd like to automatically reduce font size, if a text doesn't fit. I would use conditional formatting, but I guess that there's no Excel function available that would help here. And for some reason the font size can not be changed by conditional formatting on my system. So I'd like to use VBA, but how? The easiest way would be to determine, how much space a text needs and compare it with the cell width. But how do I get the first parameter? ...

Sorting for Blank Cells
I have a spreadsheet that looks like this: 02 01 VI $29.00 01 01 AX 01 01 OA $10.00 I want to separate the ones without a dollar value under the "01" or "02" cell. Can anyone offer any help? Thanks, Tom Hi one way: Add a helper column (lets say B) enter the following in B1 =IF(ISNUMBER(A1),"",IF(ISNUMBER(A2),"","to be filtered")) copy this down After this select this column B, copy it and insert it again as Value ('Edit - Paste Special'). Now do your sorting -- Regards Frank Kabel Frankfurt, Germany TOM wrote: > I h...

Copying cells or worksheet
Newbie-ish question - I want to copy either a worksheet or the cells in the worksheet from version A of a workbook to version B of that workbook. The contents of the cells are formulae that refer to other sheets in the workbook. When I do the copy, the result is that Excel 2000 outsmarts me and thinks I want to continue to refer to the original cells (in version A), so it inserts a reference to version A of the workbook. All I want to do is copy the formulae without any change to the formulae - is there a way to do that? The answer is probably very simple, but I can't find it - it'...

A function that returns the name of the current cell
CELL() function can return various data about the current cell, but not its name. It does, however, return row and column serial numbers, so If I want to lookup some cells in another table that contains their names, I have to use this: (CHAR(64+CELL("col",A1)))&CELL("row", A1) (assuming that the column names have only 1 letter). I suggest adding a possible value to the info_type argument of the function CELL(): "name" - Text value containing the name of the cell, for example: C19. ---------------- This post is a suggestion for Microsoft, and Microsoft res...

Protecting Cells AFTER Data is entered.
I want to make a sheet that allows people to enter data, but onc entered, it cannot be changed. Is this possible -- km ----------------------------------------------------------------------- kmd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3112 View this thread: http://www.excelforum.com/showthread.php?threadid=50787 kmd, here is one way, put in sheet code Private Sub Worksheet_Change(ByVal Target As Range) 'Automatically Protecting After Input 'unlock all cells in the range first Dim MyRange As Range Const Password = "123" &#...

cell selection problem
Yesterday my excel 2003 started to have this problem. If I select a cell, my mouse cursor goes into selection mode and I can't get out of this mode. I have hit keys, tried to put the mouse on the title bar of the window to minimize the app (and I end up selecting all the rows above the current row). The only thing I can do is alt-tab to the next application, and close excel from the taskbar window. I must have done something to start this behavior, I just can't figure out what. Hi try hitting F8 ´to disable the extended mode -- Regards Frank Kabel Frankfurt, Germany "...

Use a cell value as part of a formula
Hi All, I'm trying to use a cell value as part of a vlookup function. The idea is to keep changing the lookup query based on a the contents of a specific cell This is the forumla I'm currently using VLOOKUP($A2,'C:\Matrix0203\07-Feb03\[025-MainPub-ET-1801.XLS]Sheet1'!$A$1:$M$200,2,FALSE) I need a way to replace "025-MainPub-ET-1801.XLS" in the formula based on another cell, where this will be generated (or manually entered). Right now I'm doing a select all, find and replace to achieve this. Any help will be appreciated... Chirag Hi Chirag, You could use ...

Inserting a cell value from an "IF" function
I want to do something that confuses me to explain, but I'll give it a go. I need to be able to insert a name from one worksheet onto another, depending on a letter in another column alongside the name. I need to be able to do this for several names in a list. I wonder if this is possible as I have no real idea where to start :) Thanks! -- -Liam >I have no real idea where to start :) Start here: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "liamellis91" <liamellis91@discussions.microsoft.com> wrote in message...

Average a column that contains one cell of text
What formula do I use to average a column of numbers that contain one cell with text? I'm using Excel 2007 The AVERAGE function will ignore text (in an array). How do you want to handle that one cell with text? -- Biff Microsoft Excel MVP "Malcolm" <Malcolm@discussions.microsoft.com> wrote in message news:A62B59FC-415C-433E-897F-819865239F62@microsoft.com... > What formula do I use to average a column of numbers that contain one cell > with text? I'm using Excel 2007 =average(c:c) should ignore text. I get 4 from this sample 1 ...

Beginner: Compare cells, if they have same value, then copy the ro
Hi, I am newbie to VBA, and need help to accomplish: In a sheet I need to compare the cells in row E (row E is sorted alphabetically), to see if they have the same value. It can be up to 10 rows with the same value. (the whole sheet contains <1500 rows) If they are the same, those rows with samy value in column E shall be copied to another sheet. This is just a part of the makros that are running. As said, I am a newbie, but does this do something? (Earlier in the makro there is Dim x x = 1) Sheets("Skatteseddel").Select Dim k k = 1 ...

stopping pasted text deliminate across multiple cells
When pasting text that contains back slashes, text is split into multiple cells using the back slash as a deliminator, this occurs automatically and I wish to stop it. Pasting as text or unicode text via paste special makes no difference. Example text 'C:\Program Files\Common Files\Microsoft Shared' splits into 4 cells in a row and the back slashs removed Using Excel 2003 SP1 Paul, Did you do a Text to Columns with the back slash as the delimiter ? look at this: -- Regards, Juan Pablo Gonz´┐Żlez Excel MVP "Paul from NZ" <Paul from NZ@discussions.microsoft.com> w...

count formatted cells
I would like to count the number of cells in a column that have been formatted a certain colour. is this possible? See http://cpearson.com/excel/colors.htm In article <cassec$p6b$3@news6.svr.pol.co.uk>, "bobtracey" <bob@traceyr.freeserve.co.uk> wrote: > I would like to count the number of cells in a column that have been > formatted a certain colour. is this possible? Only with macro. You should find code with a search, this is a ver common question -- Message posted from http://www.ExcelForum.com ...

formatting cells #6
Is there a way to set the width and heighth of a cell in inches? Thanks, Debbie Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. For an interesting and enlightening discussion on this subject see http://snipurl.com/dzz8 If you want to use VBA to set height and width in mm which you can convert to inches. Ole Erlandson has code for setting row and column dimensions. http://...

Locking cells only for certain users
Is it possible to lock up certain cells in a spreadsheet to certain users if the workbook is saves on a network? For example, Jim has access to make changes to column D while Bill has access to make changes to column G? you can use an event like the workbook.open to execute some code tha changes the protection on the cells dependant upon the user. The use can be determined using the environ("username") function. you also need to protect the VBA hope this help -- tony ----------------------------------------------------------------------- tony h's Profile: http://www.exce...

how to make cell turn red or green? Please help...
In a row I have a one cell which is drop down list so I can choose a) or b) value. How can I make thet if I choose a) value, whole row turn green, or when I choose b) value, whole row turn red? Thanks... Format/ Conditional Formatting/ Formula Is =$A1="a) value" Choose green format Add>> Similarly for red. -- David Biddulph "Nevermind" <nevermind@hotmail.com> wrote in message news:g01fnn$48m$1@ss408.t-com.hr... > In a row I have a one cell which is drop down list so I can choose a) or > b) value. > > How can I make thet if I choose a) va...

Cell Protection #4
Having problem protecting cells in shared folder on shared drive. Need to protect a group of cells that are for viewing only, but leave adjacent cells unprotected. Being a shared worksheet I believe is the problem, there are limitations. Any suggestions. Thanks to all. Susan I think you'll have to do all your setup (disabling protection/locking & unlocking cells/reenabling protection) against a workbook that isn't shared. But I'm not sure if that addresses what your problem is????? Susan wrote: > > Having problem protecting cells in shared folder on shared > dr...

macro for coloring cells
is there a macro for coloring the cells up? i'd like to color up selected cells as; say by pressing "ctrl + p". hal9000 You would need to build a macro to do what yoiu whant. But why don' you use the available toolbar item that does that with one mouse clic - Available on the Formatting toolba -- Message posted from http://www.ExcelForum.com There are no macros by default. You make them yourself and get prompted "this workbook contrains macros" when you open those files afterwards. In this case, simply record a macro while coloring a cell and you're ...

Create List based on cell data
I have been trying to do this various ways and can just not get i right. I have a table that has multiple cities and populations and squar miles: A B C 1 city 1 sq mile 1 pop 1 2 city 2 sq mile 2 pop 2 3 city 3 sq mile 3 pop 3 4 city 4 sq mile 4 pop 4 5 city 5 sq mile 5 pop 5 I know how to order the list by (smallest to largest) number: Square Miles Population 10 =large(b1:b5,5) =large(c1:c5,5) 11 =large(b1:b5,4) =large(c1:c5,4) etc.. I would prefer to be able to display the name of the city (instead o the s...

write notes to a cell
Can I write a note to a cell that already has an alternate function -- Message posted from http://www.ExcelForum.com Or Not -- Message posted from http://www.ExcelForum.com OK... I'm not sure if this is what you're looking for... but have yo tried entering a comment? Rightclick the cell, Click on "Insert Comment" Then type whatever you want the note to read. After that, all you have to do to read the note is to hover your curso over the cell and a little box will appear displaying the information. HTH Jos -- Message posted from http://www.ExcelForum.com You are so...

how to set color in cells dependent on what letter you enter?
how do i set up excel in a spreadsheet so that when i enter in "W" the cell will turn yellow as the background for that cell, or if i enter "R" itll be blue indiciating that all cells with the letter R (for repairs) will be turned blue? thanks! -- illmaticnyc ------------------------------------------------------------------------ illmaticnyc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24291 View this thread: http://www.excelforum.com/showthread.php?threadid=378977 Take a look at conditional formatting in Help. In article <illmat...

? avoid changing sum function as rows added?
Hello Group: I'm obviously new. To keep it simple, here's how I'll pose the question: Suppose I have multiple rows and two columns. Column A contains names of people Column B contains each person's age The last cell in column B is to be an average of the ages, so in that cell I insert the function =AVERAGE(B1:B100) This works fine, but as I add rows, I have to change the formula to include the recently added row, B101, then B102, etc) Is there any way I can make the function automatically include the last cell? Sort of like a wild symbol? It's not a big problem, but I...

Converting varying length Vertically-aligned records to Horizontal Rows of list (tabular) #2
I have a Excel Spreadsheet with Records of books. The fields and data value is entered as the First and Second Column (ColA, ColB). The records are separated by 2 empty rows. Some fields are missing for some records: Some record may miss the 'author' whereas some records may miss some other fields, say ,'subject' AND 'totalpage'. The Objective is to tranform the record horizontally with Each ffield in one column with missing field blanked so that all the field are aligned in a column e.g. colA for 'Title', colB for 'Author' How can we convert varying ...

How to go back to last selected cells?
Hello How to go back to last selected cells? Hi Dmitri, Try something like: '===================>> Public Sub Tester03() Dim rng As Range Dim rCell As Range Set rng = Selection Set rCell = ActiveCell 'your code, e.g. Range("A1").Select rng.Select rCell.Activate End Sub '<<=================== However, the above code would not normally be needed because it is rarely necessary, or desirable, to make selections, --- Regards, Norman "Dmitry Kopnichev" <kopn@bk.ruDelete> wrote in message news:uzAUQ1%23...

combining two cell values to one value to chart
Hello While making a chart I wish to combine two values from two seperate cells to make one value to send to a chart. In this case it is the values for the columns named S3 and S4. Enclosed is the ss please refer to sheet 3. I don't know the formula for adding the two and displaying. It's a bit confusing this ss formatting. The S3 col are L and S4 is N. I don't know if I have to make the calc show in a cell or just do it on the fly. Using the chart wizard it's the series values I believe is where a formula could go. thanks for any help given, Steve Sorry, looks like I ...

Excel Cell Protection / Locking Problem
Is there a way to protect formulas in a sheet (that is, locking certai cells and protecting the sheet) while still enabling users to add line in unlocked areas of the sheet? I am trying to develop a price quoting form where the user can ad extra lines (if additional line items are added) while I keep th structure of the sheet (formulas that total columns, calculate tax an so forth) intact. I have a feeling that once I protect a sheet, I am foreclosed fro adding line no matter what. Thanks -- Message posted from http://www.ExcelForum.com have you used Data Validation? This restricts wha...