#### GENERATE RANDOM NUMBERS BUT EXCLUDE A NUMBER IN THE SEQUENCE

```I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE WHATEVER
IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED TO GENERATE
1,2,3,4,5,6,7,OR 9. IM STUMPED?
```
 0
Tracker (7)
8/4/2005 9:16:01 PM
excel.misc 78881 articles. 5 followers.

6 Replies
724 Views

Similar Articles

[PageSpeed] 2

```> I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE
> WHATEVER IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED
> TO GENERATE 1,2,3,4,5,6,7,OR 9. IM STUMPED?

On way:

In A2:A100, put
=RANDBETWEEN(1,8)

In B1, put
=RANDBETWEEN(1,9)

In B2, put
=A2+(A2>=B1)
and copy down to B100

Use the values in column B.
```
 0
8/4/2005 10:03:37 PM
```I might of explained it wrong. I want to use random(1,9) but for example i
dont want 5 in the random possibility.Is that possible?

"Jay" wrote:

> > I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE
> > WHATEVER IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED
> > TO GENERATE 1,2,3,4,5,6,7,OR 9. IM STUMPED?
>
> On way:
>
> In A2:A100, put
>   =RANDBETWEEN(1,8)
>
> In B1, put
>   =RANDBETWEEN(1,9)
>
> In B2, put
>   =A2+(A2>=B1)
> and copy down to B100
>
> Use the values in column B.
>
```
 0
Tracker (7)
8/4/2005 10:44:02 PM
```Did you try "Jay"s solution? If you copy A2:B2 down as far as desired,
you get results (in column B) for each cell in the range [1...9]
excluding the value in the "cell above it".

This post, at least as far as I can tell, doesn't add any information
that would indicate that "Jay"s solution doesn't fit.

Do you *always* want to exclude the same number? Do you want to exclude
more than one number (e.g., 8 and 5)?

In article <D60D29CA-93DD-4E73-9B80-423FD258C294@microsoft.com>,
"Tracker" <Tracker@discussions.microsoft.com> wrote:

> I might of explained it wrong. I want to use random(1,9) but for example i
> dont want 5 in the random possibility.Is that possible?
```
 0
jemcgimpsey (6723)
8/4/2005 11:00:00 PM
```Yes that helps.but lets say i needed to randomize 1-9 in nine cells that
exclude the number in the top cell of the nine cells?

"JE McGimpsey" wrote:

> Did you try "Jay"s solution? If you copy A2:B2 down as far as desired,
> you get results (in column B) for each cell in the range [1...9]
> excluding the value in the "cell above it".
>
> This post, at least as far as I can tell, doesn't add any information
> that would indicate that "Jay"s solution doesn't fit.
>
> Do you *always* want to exclude the same number? Do you want to exclude
> more than one number (e.g., 8 and 5)?
>
>
> In article <D60D29CA-93DD-4E73-9B80-423FD258C294@microsoft.com>,
>  "Tracker" <Tracker@discussions.microsoft.com> wrote:
>
> > I might of explained it wrong. I want to use random(1,9) but for example i
> > dont want 5 in the random possibility.Is that possible?
>
```
 0
Tracker (7)
8/4/2005 11:11:04 PM
```Tracker,

Another way, which might be a little more complicated is to use IFs for
different ranges.  For example, if you wanted a random number between 1-4 or
6-9 (ie, not 5) you could make a formula that 50% of the time gives a number
between 1 and 4 and 50% of the time gives a number between 6 and 9.  This
one's easy though, because it's 50-50.  If you want to remove numbers other
than 5, you'd have to modify the proportions.

=if( rand() < 0.5, RANDBETWEEN(1,4), RANDBETWEEN(6,9) )

Let's see.  If you're always working between 1 and 9, and the number you
want to exlude is "X", then:

=if( rand() < (X-1)/8, RANDBETWEEN(1,X-1), RANDBETWEEN(X+1,9) )

I have to run, so I haven't checked if this formula works, but it or
something like it should do the job.

Eric

"Tracker" wrote:

> Yes that helps.but lets say i needed to randomize 1-9 in nine cells that
> exclude the number in the top cell of the nine cells?
>
> "JE McGimpsey" wrote:
>
> > Did you try "Jay"s solution? If you copy A2:B2 down as far as desired,
> > you get results (in column B) for each cell in the range [1...9]
> > excluding the value in the "cell above it".
> >
> > This post, at least as far as I can tell, doesn't add any information
> > that would indicate that "Jay"s solution doesn't fit.
> >
> > Do you *always* want to exclude the same number? Do you want to exclude
> > more than one number (e.g., 8 and 5)?
> >
> >
> > In article <D60D29CA-93DD-4E73-9B80-423FD258C294@microsoft.com>,
> >  "Tracker" <Tracker@discussions.microsoft.com> wrote:
> >
> > > I might of explained it wrong. I want to use random(1,9) but for example i
> > > dont want 5 in the random possibility.Is that possible?
> >
```
 0
Eric1 (389)
8/5/2005 12:09:02 AM
```Sounds like that will work.Thankyou

"Tracker" wrote:

> I NEED TO GENERATE A RANDOM NUMBER BETWEEN 1-9 BUT NEED TO EXCLUDE WHATEVER
> IS IN THE CELL ABOVE IT. IF THE CELL ABOVE IT SAYS 8. I NEED TO GENERATE
> 1,2,3,4,5,6,7,OR 9. IM STUMPED?
```
 0
Tracker (7)
8/5/2005 1:49:03 AM

Similar Artilces:

Dynamically generating email as html from a template
Would anyone know of an article/information detailing the creation of templates in word that can be used to create word documents by merging data to be saved as html to be included as the body of an email in outlook. .... or another method of dynamically creating emails in outlook based on a template. Thanx for the Advice Have you looked at Word's mail merge feature? That's exactly what it = does. See http://www.slipstick.com/contacts/startletter.htm=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Us...

Format numbers in Textbox
I have a textbox that is linked to a cell. That cell contains currency type data. How do I set the formatting of the text box to carry over that data and format it with \$ and ,'s Text boxes treat everything as text, even when entries appear numeric. So there is no built-in number formatting option available. -- Jim "stickandrock" <stickandrock@discussions.microsoft.com> wrote in message news:AD1D92B7-7079-4D18-81C4-A68CA58ADD20@microsoft.com... |I have a textbox that is linked to a cell. That cell contains currency type | data. How do I set the formatting of th...

intergrating row number into a formula
Ok gurus, Here's another one. I want to pull a value out of a certain column based on the row that the formula is in. For example: -The desired value is in A column (number value) -The row() function will return the row that the formula is in -Lets say row returns 13 -I would like to retreive the text value in A13 dynamically using row() within the formula.....something like ="S_"&"5133"&"_"&TEXT(C3,"dd") -The "5133" would be the text value of the number from A13 as determined by row Sorry if these are noob ques...

ExcelReport
Please access http://www.ljzsoft.com ExcelReport is a report generator to generate reports in Microsoft Excel format. If you know how to use Excel and write SQL statements, you can use ExcelReport to create all kinds of reports as you need. Furthermore, with one time configuration, you can easily build periodic reports such as daily, weekly, monthly and annual reports. ...

Error Generating the Offlice Address Book
I have a mixed site with 3 5.5 server and 4 2003 servers. I installed 2003 SP1 a few weeks back and since then I'm having an issue generating my Offline Address Book. Here the event log messages I'm getting. Event ID 9331: OALGen encountered error 80040107 (internal ID 501023d) accessing the public folder store while generating the offline address list for address list '/'. - Default Offline Address List For more information, click http://www.microsoft.com/contentredirect.asp. Event ID 9335: OALGen encountered error 80040107 while cleaning the offline address list public ...

Preventive Maintenance, multiple call generation 1 generate to pr.
need to be able to have preventive maintenance generate a call for each day in a month when running the generate routine. Rather than having to create each one individually. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/...

Random assignment
I have three tables CREATE TABLE [dbo].[STAFF]( [StaffKEY] [int] NOT NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[Clients]( [ClientKEY] [int] not NULL, [ClientName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[STAFF_Collections]( [CLIENTKEY] [int] NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Staffkey] [int] NULL ) ON [PRIMARY] Sample data Staff: 303, 'Paul Newman' 405, 'Jane Fonda' 605,&#...

PROVEN SYSTEM TO GENERATE CASH
Hi friends, I think it be a very promising program .... the great thing about it is they market it for you! Easy, once you join all of this happens without you lifting a finger! You automatically received your replicated Unique URL (personal web site) You automatically have 150,000+ emails sent to promote your web site You automatically have people (just like you) visit your web site You automatically have welcome emails sent to your new prospects You automatically have 10 days of follow-up (drip) emails sent to your new prospects The System automatically processes the new member when they...

page numbering in excel #5
How can I have page numbers show up in portrait style while the data is in landscape? Is this possible? ...

vs2005 xsd.exe: Can it generate different files?
Is there an option to get the vs2005 xsd.exe tool to generate seperate files for each class that it generates? It would be much easier from a maintenance standpoint than one big giant file for my classes. Thanks for any help Matt MattBell wrote: > Is there an option to get the vs2005 xsd.exe tool to generate seperate files > for each class that it generates? I had a look at the output of xsd.exe /help but there is no option/setting mentioned. -- Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/ ...

find duplicate numbers in a column?
Hi! i have a spreadsheet that is accessed by 12 different users all day. in column A we enter stock numbers. Is there a way to have Excel let me know if there are ever duplicate stock numbers entered? i can insert new columns to do this...just not sure what formulas i would use to do that?? any help is greatlly appreciated!! thanks in advance! -- JENNYC ------------------------------------------------------------------------ JENNYC's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19579 View this thread: http://www.excelforum.com/showthread.php?threadid=481999 ...

Dlookup Problem Text VS Number type
Hello all- I have a form (in access) that has a section of code which fills in certain cells for an excel spread sheet. Code has worked perfectly for a while, but recently I changed the table's 'OrderNumber' field from a Number type to a Text type. Now my Dlookup doesn't work. I tried changing it back, and it works again. Why does it matter? I need/would really like it to be a text type field. All help/explinations would help a lot. Thanks! Zach Zach - Did you also update the DLookup statement? A text field requires the single-quote delimiter arou...

to generate only table names but not their schema in the database
My replication on multiple servers failed. So i want to know which articles are published from the daabase. i wish to generate only articles that are there in the database, but not their schema or column names. how to do this through ms visio ...

Auto-generate product ID
Is there a way to auto-generate product ID? Or is there any third party addons that does that? Better yet would be a way to auto-generate a barcode for each item "Raymond" <Raymond@discussions.microsoft.com> wrote in message news:F1EF7ACB-8D15-4527-A8C7-53C9F8FD6906@microsoft.com... > Is there a way to auto-generate product ID? Or is there any third party > addons that does that? Raymond, We offer AutoGen for this purpose. Barcode 128A is the default for new item adds. For more information contact your RMS reseller or visit: http://www.digitalretailer.com/rmsaddin...

Auto Generate Serial Numbers for Bill of Materials
Hi! We have a partner in Australia who has some issues with the Serial Number Masks for Finished Goods in Inventory. Currently Bill of Materials functions this way: If the customer creates 20 finished products which tracks Serial Numbers. The user has to manually enter the 20 serial numbers into Assembly Entry. Even though the Finished product has been setup with a Serial Mask, there is no option to Auto Generate Serial Numbers. He would like to suggest that there should be an auto generate serial number function also for the finished goods in Bill of Materials. Thanks, ---------...

I need a message if number of duplicate records exceeds number allowed
Thanks in advance for any help. I'm trying to create If DCount code on my form that will count the number of duplicate records and then notify me when the number of duplicate records exceeds the number allowed. The number of duplicates allowed is established in tblFloorProgCriteria in the field FloorProgMaxObservations. Here's what I have so far...It's giving me the message as soon as I attempt to add the first record. If DCount("*", "tblFloorProgAudit", "[AuditID] = " & Me.[AuditID] & " And [FloorProgCriteriaID] = "...

how to generate a range of number
Howdy folks, I would like to know how to generate a range of number (for example: 10000 to 19999) in excel without typing them in each number into each cell one by one. Your help would boot up my business significantly. Gracias, Vito Corleone, President Check out the Edit|Fill, Series options. If you want the numbers to go down a column, make sure you click the Columns choice. tj "Vito Corleone" wrote: > Howdy folks, > > I would like to know how to generate a range of number > (for example: 10000 to 19999) in excel without typing them > in each number int...

Number of digits problem
Hi everyone I have this number in Cell A1 = 6190099009 Obviously " =Len(A1) will return 10, =Left(A1,1) will return 6. If I combine this conditions together: (Return "True" if it starts with 6 and if it is 10digits. =if(and(Len(A1)="10",Left(A1,1)="6"),"True","False") However, it returns "FALSE". Why does it return false when I join them by "AND"??? Please Help Thank you. James8309 <jaedong1221@gmail.com> wrote in news:41d70b61-47ae-4867-989c- 801891b49752@w34g2000prm.googlegroups.com: > =if(and(Len(...

Newly Mailbox not being generated
I am running Windows 2003 and Exchange 2003 I have created new users and selected to create a mailbox and they don't show. I have created mailboxes by using the exchange task wizard and still the same thing. I have update the "Recipient Update Service" and Recipient Policies and still no luck. Any ideas would be appreciated. Thank You Terry How are you determining that the mailboxes are not being created? Are you looking under the Mailbox Store->Mailboxes in the Exchange System Manager? The mailbox will not show up there until it is initialized (the user logs ...

number of worksheets
How many worksheets a workbook may have? In EXCEL 2007, according to MS documentation:- "Limited by available memory (default is 3 sheets)" Please hit Yes if my comments have helped. Thanks. "Neia" wrote: > How many worksheets a workbook may have? Limited by your pc's memory. Neia wrote: > > How many worksheets a workbook may have? -- Dave Peterson Hi, As many as your memory can handle "Neia" wrote: > How many worksheets a workbook may have? Limited by memory -- Don Guillett Microsoft MVP Excel Sa...

File-list generated from folder including attributes
Hey guys My macro generates a file-name list with accordingly attributes as "size", "Accessed date". My problem is to expand this Explorer-information with attributes: "Author" and "Owner". I dont know/cantv find the code...... maybe file.author or something????+ Here is my macro: Sub SelectFiles(ByVal sPath) '----------------------------------------------------------------------- Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Set Folder = FSO.GetFolder(sPath) Set Files = Folder.Files...

How do I randomize words within a row?
I need to set up a random order list for 3 studies I'm running. We'll call them A, B and C. I need each of my subjects to run A,B,C but in a random order. Is there a way to randomize within rows in Excel...that seems to be the only way I can think of to creat this list(i have about 100 subjects). Any suggestions would be greatly appreciated. One was to do it would create a column (call it sort) in that column I would use the Function =RAND() This will generate a random number between 0 and 1. Then you sort the file by the Sort Column and that should randomize it. Note that ever...

class generated from XSD.exe will not generate XML?
Using this command line to run XSD.exe xsd /c /o:outputDir input.xsd /f on the following XSD <?xml version="1.0"?> <schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:dino="http://example.com/test" targetNamespace="http://example.com/test" elementFormDefault="qualified"> <element name="dinosaurs"> <complexType> <attribute name="version" type="decimal" fixed="1.0" /> <attribute name="source" type="normalizedString" /> <attribute ...

How to convert number to text
Hi, Need your professional guys' help. How can I conver specific inot specific text in excel table, not only one data. I had used "TEXT" function, EX: =TEXT(2,"good"). It was work but only applied on one data input. My Question is: 1) How can I apply server "TEXT" functions into one Excel sheet at one click? Thanks in advance. Hi why not use 'Edit - Replace' -- Regards Frank Kabel Frankfurt, Germany "Goody" <Goody@discussions.microsoft.com> schrieb im Newsbeitrag news:8957703A-8D72-42B8-9356-7519492FC4DA@microsoft.com... &g...

Color Rule Number Limitations?
Hi Is there a limitation on the number of color rules one can specify using outlook 2002? I appear to have exhausted this number and cannot specify nor designate a color from a new e-mail recipient. Please advise. Thanks. ...