Help on Random Numbers

Hi.
It would help to know how I could 
Fill up random numbers in say Cell A1 to E1
but the total of the random numbers should add up to say number 100
please help.
0
Sara (51)
2/15/2005 4:15:01 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
480 Views

Similar Articles

[PageSpeed] 1

Hi

one option is in cells
A1:D1 use the formula
=RANDBETWEEN(1,20)
and in E1 use the formula
=100-SUM(A1:D1)

Cheers
JulieD

"sara" <sara@discussions.microsoft.com> wrote in message 
news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
> Hi.
> It would help to know how I could
> Fill up random numbers in say Cell A1 to E1
> but the total of the random numbers should add up to say number 100
> please help. 


0
JulieD1 (2295)
2/15/2005 6:37:15 AM
Hi JuliD
tried that. works fine. but sometimes cell E1 displays number that is too 
large
from the rest of the cells. This is because the total is subtracted from 100.
I would prefer  "evenly" distributed numbers. Are there any formulars to do 
that ?

"JulieD" wrote:

> Hi
> 
> one option is in cells
> A1:D1 use the formula
> =RANDBETWEEN(1,20)
> and in E1 use the formula
> =100-SUM(A1:D1)
> 
> Cheers
> JulieD
> 
> "sara" <sara@discussions.microsoft.com> wrote in message 
> news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
> > Hi.
> > It would help to know how I could
> > Fill up random numbers in say Cell A1 to E1
> > but the total of the random numbers should add up to say number 100
> > please help. 
> 
> 
> 
0
Sara (51)
2/15/2005 7:27:03 AM
Hi Sara

not aware of any way of achieving this - even using code it would be 
difficult as you'ld have to "loop" it until you found an "evenly 
distributed" series of numbers and then one would question the concept of 
"random", especially as you're only taking about 5 numbers!

sorry

Cheers
JulieD



"sara" <sara@discussions.microsoft.com> wrote in message 
news:6FA16BAE-44F4-4C00-A533-F4ED41E349D4@microsoft.com...
> Hi JuliD
> tried that. works fine. but sometimes cell E1 displays number that is too
> large
> from the rest of the cells. This is because the total is subtracted from 
> 100.
> I would prefer  "evenly" distributed numbers. Are there any formulars to 
> do
> that ?
>
> "JulieD" wrote:
>
>> Hi
>>
>> one option is in cells
>> A1:D1 use the formula
>> =RANDBETWEEN(1,20)
>> and in E1 use the formula
>> =100-SUM(A1:D1)
>>
>> Cheers
>> JulieD
>>
>> "sara" <sara@discussions.microsoft.com> wrote in message
>> news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
>> > Hi.
>> > It would help to know how I could
>> > Fill up random numbers in say Cell A1 to E1
>> > but the total of the random numbers should add up to say number 100
>> > please help.
>>
>>
>> 


0
JulieD1 (2295)
2/15/2005 7:33:32 AM
Hi Sara
You could do this:
In A1 to E1, type formula =rand(). 
In A2 to E2 type =A1*100/SUM($A$1:$E$1) (where 100 is the sum of all the 
random numbers you require)
This will give you the numbers whose some equals 100.


"sara" wrote:

> Hi JuliD
> tried that. works fine. but sometimes cell E1 displays number that is too 
> large
> from the rest of the cells. This is because the total is subtracted from 100.
> I would prefer  "evenly" distributed numbers. Are there any formulars to do 
> that ?
> 
> "JulieD" wrote:
> 
> > Hi
> > 
> > one option is in cells
> > A1:D1 use the formula
> > =RANDBETWEEN(1,20)
> > and in E1 use the formula
> > =100-SUM(A1:D1)
> > 
> > Cheers
> > JulieD
> > 
> > "sara" <sara@discussions.microsoft.com> wrote in message 
> > news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
> > > Hi.
> > > It would help to know how I could
> > > Fill up random numbers in say Cell A1 to E1
> > > but the total of the random numbers should add up to say number 100
> > > please help. 
> > 
> > 
> > 
0
Sukhjeet (13)
2/15/2005 7:41:03 AM
If the numbers must add to 100 the last number is not really random 
because there's a constraint involved. Of course the RAND function 
really gives you pseudo-random numbers anyway, good enough for most 
practical purposes.

You can avoid getting a large number as the fifth number by changing the 
upper limit of RANDBETWEEN to a number higher than 20. Using 20 as the 
upper limit guarantees that the fifth number will always be 20 or 
greater. Don't go too high or the fifth number will be too small 
compared to the others. You'll have to experiment a bit.

Bill

"sara" <sara@discussions.microsoft.com> wrote in message 
news:6FA16BAE-44F4-4C00-A533-F4ED41E349D4@microsoft.com...
Hi JuliD
tried that. works fine. but sometimes cell E1 displays number that is 
too
large
from the rest of the cells. This is because the total is subtracted from 
100.
I would prefer  "evenly" distributed numbers. Are there any formulars to 
do
that ?

"JulieD" wrote:

> Hi
>
> one option is in cells
> A1:D1 use the formula
> =RANDBETWEEN(1,20)
> and in E1 use the formula
> =100-SUM(A1:D1)
>
> Cheers
> JulieD
>
> "sara" <sara@discussions.microsoft.com> wrote in message
> news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
> > Hi.
> > It would help to know how I could
> > Fill up random numbers in say Cell A1 to E1
> > but the total of the random numbers should add up to say number 100
> > please help.
>
>
> 


0
bsharpe (111)
2/15/2005 11:37:10 PM
Hi Bill
The method I proposed, of generating five random numbers, multiplying each 
with the total required (100 in this case) and then dividing with the sum of 
those five random numbers will result in a better randomisation than 
generating 4 random numbers (between 1 and 20) and subtracting their sum from 
100. 
Ciao
Sukhjeet

"Bill Sharpe" wrote:

> If the numbers must add to 100 the last number is not really random 
> because there's a constraint involved. Of course the RAND function 
> really gives you pseudo-random numbers anyway, good enough for most 
> practical purposes.
> 
> You can avoid getting a large number as the fifth number by changing the 
> upper limit of RANDBETWEEN to a number higher than 20. Using 20 as the 
> upper limit guarantees that the fifth number will always be 20 or 
> greater. Don't go too high or the fifth number will be too small 
> compared to the others. You'll have to experiment a bit.
> 
> Bill
> 
> "sara" <sara@discussions.microsoft.com> wrote in message 
> news:6FA16BAE-44F4-4C00-A533-F4ED41E349D4@microsoft.com...
> Hi JuliD
> tried that. works fine. but sometimes cell E1 displays number that is 
> too
> large
> from the rest of the cells. This is because the total is subtracted from 
> 100.
> I would prefer  "evenly" distributed numbers. Are there any formulars to 
> do
> that ?
> 
> "JulieD" wrote:
> 
> > Hi
> >
> > one option is in cells
> > A1:D1 use the formula
> > =RANDBETWEEN(1,20)
> > and in E1 use the formula
> > =100-SUM(A1:D1)
> >
> > Cheers
> > JulieD
> >
> > "sara" <sara@discussions.microsoft.com> wrote in message
> > news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
> > > Hi.
> > > It would help to know how I could
> > > Fill up random numbers in say Cell A1 to E1
> > > but the total of the random numbers should add up to say number 100
> > > please help.
> >
> >
> > 
> 
> 
> 
0
Sukhjeet (13)
2/16/2005 5:33:01 AM
Hi Guys,
Thanks for the brilliant suggestions. I prefer Sukhjeet's method as JulieD
suggestion; at random produces negative number on the last cell and also 
random numbers appear procedural in the first 4 cells but not in the last 
one(i.e if at random A1 to D1 produces nos 1,4,6,6.then the last one produces 
100-1-4-6-6.this is a huge different) I am just afraid if this random row 
might go unnoticed. Thanks guys. And by the way I know F9 randoms the entire 
spreadsheet but how do I random just one row out of the hundreds?  



"Sukhjeet" wrote:

> Hi Bill
> The method I proposed, of generating five random numbers, multiplying each 
> with the total required (100 in this case) and then dividing with the sum of 
> those five random numbers will result in a better randomisation than 
> generating 4 random numbers (between 1 and 20) and subtracting their sum from 
> 100. 
> Ciao
> Sukhjeet
> 
> "Bill Sharpe" wrote:
> 
> > If the numbers must add to 100 the last number is not really random 
> > because there's a constraint involved. Of course the RAND function 
> > really gives you pseudo-random numbers anyway, good enough for most 
> > practical purposes.
> > 
> > You can avoid getting a large number as the fifth number by changing the 
> > upper limit of RANDBETWEEN to a number higher than 20. Using 20 as the 
> > upper limit guarantees that the fifth number will always be 20 or 
> > greater. Don't go too high or the fifth number will be too small 
> > compared to the others. You'll have to experiment a bit.
> > 
> > Bill
> > 
> > "sara" <sara@discussions.microsoft.com> wrote in message 
> > news:6FA16BAE-44F4-4C00-A533-F4ED41E349D4@microsoft.com...
> > Hi JuliD
> > tried that. works fine. but sometimes cell E1 displays number that is 
> > too
> > large
> > from the rest of the cells. This is because the total is subtracted from 
> > 100.
> > I would prefer  "evenly" distributed numbers. Are there any formulars to 
> > do
> > that ?
> > 
> > "JulieD" wrote:
> > 
> > > Hi
> > >
> > > one option is in cells
> > > A1:D1 use the formula
> > > =RANDBETWEEN(1,20)
> > > and in E1 use the formula
> > > =100-SUM(A1:D1)
> > >
> > > Cheers
> > > JulieD
> > >
> > > "sara" <sara@discussions.microsoft.com> wrote in message
> > > news:4AC3785B-5F5F-4884-BBDB-25FDF36096DD@microsoft.com...
> > > > Hi.
> > > > It would help to know how I could
> > > > Fill up random numbers in say Cell A1 to E1
> > > > but the total of the random numbers should add up to say number 100
> > > > please help.
> > >
> > >
> > > 
> > 
> > 
> > 
0
Sara (51)
2/17/2005 2:03:01 AM
Reply:

Similar Artilces:

HELP! #2
I just had a cluster failover and fail back and for whatever reason, now when users go into Outlook and type the name of a public folder or newsgroup on the To: line they see the name of the group plus some sort of SID looking string next to it. For example, instead of just seeing thename of the group they are seeing: company.newsgroupname <newsgroupname34239492038902385092845923> What's going on? Help! Dan Dan, Are there any errors in the event logs pertaining to access of AD or anything like that? C. Smith Enso Technologies, Incorporated http://www.ensotech.com On Wed,...

Iterative Calculation help needed
I have the following information A1 - Balance at the beginning of calculation A2 - Annualized payment A3 - Interest rate divided by # of payments per year A4 - Number of years * # of payments per year I'm trying to calulate the total ((A1-A2)*(1+A3)) - I need to do this over and over up until I've don it A4 times. I can do it for each period but I want to do it all in one cell, i possible. How can I have it plug the new number into A1 and repeat th calculation A4 times. Thanks -- Message posted from http://www.ExcelForum.com Hi there You might find that the PMT function coul...

Formula help #3
Sheet1 has a name in A1 & a number in B1, name in A2 number in B2 and s on (numbers will continually be changed). On sheet2 I used =a or = and so on (the results of =a or =b are the names from sheet 1). Wha I want to accomplish is every time I type =a or =b and the name appear I also want the result from sheet1 B1 and so on to appear in the cel directly below. Not very experienced just learning. Looking fo help -- Message posted from http://www.ExcelForum.com Hi Ljsimm You post is a bit vague, but if you mean you want B1 on sheet 2 to match up with the name on Sheet 1. Try: =IF(...

i am receiving every emai twice?? can anyone help with this issue
I would appreciate any advice cheers, See http://www.howto-outlook.com/faq/duplicates.htm -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "ETA" <ETA@discussions.microsoft.com> wrote in message news:5939A41A-2CBC-415A-BA84-C14C2FD19084@microsoft.com... > I would appreciate any advice > > cheers, ...

sequential number
I want to a sequential number to fill in automatically each time the form is filled out. Malissa, A simple way would be to use something like this, you could assign it to a button, an open or before print event. Sheets("Sheet1").Range("A1").Value = _ Sheets("Sheet1").Range("A1").Value + 1 For other ways to do this or if this is going to be used in a temple have a look here http://www.mcgimpsey.com/excel/udfs/sequentialnums.html -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others...

Emailing Tracking Numbers
I have integrated a website with RMS. When orders come in, I make them a workorder and send a PO to the distributor that will ship the product. At the end of the day, the distrbutor sends me the tracking number. When I bring up the customers order and enter the tracking number, I then complete the transcation, then press Shift/Control/F8 to mark the order processed. Does this send the tracking number to the customer? If not, how can I do that with out going to Outlook and physically doing so? Bill, RMS does not send one. Sounds like a good idea though. Maybe add it to the sugge...

Question on Returns to Vendor without PO & Receipt number
Our company switched to Great Plains Dynamics from Simply Accounting 3 months ago. We currently need to return some of the inventory items to our vendors. However, when we try to enter a transaction entry in Purchasing/Returns Transaction Entry, in the detail lines we are asked for the PO number or the receipt number. We are not allowed to go further or save without the PO number or receipt number. When we converted from Simply Accounting, all we entered was the beginning balance of the vendors and only the PO's which we expect to receive. The older PO's were not carried over. W...

Need HELP with Exchange Restore/Recovery
Details: 2003 sp2 Exchange Server Enterprise 2003 sp1 Server Enterprise C: drive is 100GB Logical Drive over x4 36GB drives in a RAID 5 set D: drive is 280GB Logical Drive over x5 70GB drives in a RAID 5 set Domain Controller Last night I upgraded our 2003 sp1 Exchange to sp2 + any O/S hot fixes and DST fix. No problem with the upgrade or hot fix, but @ about 200 a.m. I lost two of the five drives in the RAID 5 set (drive D) that had the exchange install. I’m currently replacing the hardware & bringing up the drives & going to recreate the D: drive. (server is bootable) I h...

Export to excel from ASP.Net
I have a page wherein the contents of a datagrid are exported to an excel file. All the columns are correctly displayed in excel except for the number columns. The formatting string for the number column is {0:#,##0.00; (#,##0.00);0.00}. The data is formatted for all numbers exceeding 1000 but for any number below 1000 the data is displayed as 1000 and not with 0's in the decimal places. (displayed as 999 instead of 999.00). ...

Select a certain number of cells in a row
Hi, I'd like to have the macro to select row 5 to 10 in the active column. May I know what is the VB code to write? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with I'm not quite sure why you want to select that range. But for the most part, if you act directly on the range (and avoid .selects), you're code will work faster and be easier to modify. Dolph...

Help!! Need Recovery!
So I went to open my excell file as usual and suddenly I have 10 or so instances openned. As I was closing them I saved one that turned out to be an old version, REAL OLD, like 2 weeks or so. No I desparataly need to get back to where I was. I tried to use the recovery utility under Microsoft Tools, but there are no available dates to revert to. I have no other way of reverting the document. Anyone know what to do? Any help would be greatly appreciated! Thank you.- Kjellster :D ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and ...

Sloooow. PLEASE HELP
Hi All, I have two questions first is where do I find the Windows 7 Disscussion Group. Have posted here because was unable to locate the windows 7 Discussion Group. My computer which is a SAMSUNG N110 3 months old has become very very very slow. It has Windows 7 installed I have upgraded the memory to two GIG. I have antivirus software and Anti Malware bytes installed. The problem i have is that the machine seems to slow down and sometimes crash whenever I click on internet explorer and many other applications such as office 2007 etc, etc. I have run the Norton Virus pr...

Window's verson numbers
I'm writing an app in C++ using VS.net and I need to do this based on the OS. I can get the OS version number using _osver global variable found in the include STDLIB.H. But I need a list of what version number correspond to what OS so I know what the value in _osver means. Does anyone know where I can find these definitions? Also the major and minor builds might be useful? If this is the wrong newsgroup could you direct me to the right one? Thank Lots, Neil Neil B wrote: >I'm writing an app in C++ using VS.net and I need to do this based on the OS. >I can get the OS ver...

2007 Slide Numbering doesn't work
I have a presentation in which the numbering doesn't work...I've gone step by step through everything I can find but I don't see it. Checked the master to ensure a placeholder was there, that the text is visible. I click on the slide thumbnail, click insert / slide number, check off slide numbering, click apply to all and okay. Nothing appears. When I go back to insert / slide number, nothing is checked. Any thoughts? Try ticking the option for slide number in Insert | Header and Footer instead. -- Echo [MS PPT MVP] http://www.echosvoice.com What's ne...

Help to build a table
Please help me to build a table similar to this one The number on the first column will change after 16 times, it will go in sequence from 1001, 1002, etc… The number in the second column will change after 4 times and it will go from 1 to 4 The number in the 3rd column will go from 1 to 4 2nd and 3rd column will keep the same pattern. Cabinet Shelf Location 1001 1 1 1001 1 2 1001 1 3 1001 1 4 1001 2 1 1001 2 2 1001 2 3 1001 2 4 1001 3 1 1001 3 2 1001 3 3 1001 3 4 1001 4 1 1001 4 2 1001 4 3 1001 4 4 1002 1 1 1002 1 2 1002 1 3 1002 1 4 1002 2 1 1002 2 2 1002 2...

My computer has stopped recognizing my memory card
This just happened today. The card was purchased the day after Thanksgiving so it's very new. It is a 4GB SD card from HP. I don't believe this is a hardware problem as my digital camera Lexar 1GB Memory Stick Duo card and adapter worked just fine today in the same slot. There has been no damage to the card. It has a write-protection lock on the side and I've tried the card with and without the lock on - neither way worked. I've also checked "My Computer" and confirmed that the computer is not listing my card as a drive any longer. I really need to get t...

macro to generate next number
Hi, i need a macro to look in sheet 2 column A and look at the last filled cell. In the last filled cell i have a number. When i run macro, i need to generate (in sheet1 A1) the number from the last filled cell +1. EX: sheet 2 last filled cell = 29 sheet1 after macro i need to have in A1 = 30 Can this be done? Thanks! Hi, Yuo can have a macro if you want but you don't need one, try this =OFFSET(Sheet2!A1,COUNTA(Sheet2!A:A)-1,0) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still suffici...

List number of permutations for text nums
I've got 4 digit text nums in A1 down In B1 down, I would like to list the corresponding number of permutations, eg: 7777 1 1777 4 0044 6 2477 12 1234 24 Game for any formula, udf or other vba solution Thanks for insights Sub permutations() Dim I As Integer, J As Integer, Rng As Integer Rng =3D Cells(Rows.Count, "B").End(xlUp).Row J =3D 1 Range("A1").Select Do Until ActiveCell.Value =3D "" For I =3D 1 To Rng Cells(J, "D").Value =3D "'" & ActiveCell & Cells(I, &qu...

Help writing a formula
I have a huge database that I need to create numbering for each line like this example: AK-233 (2 letter state, a dash, and 1-4 digit number), but the number doesn't change each line - sometimes the number will be duplicated on multiple lines because those lines have the same title, I also need to create another cell extending that letter/number combo that will differentiate the 1st cell. So the first cell stays the same if the title is the same. The second cell adds another layer of numbering to differentiate the first cell. See example: (Row1, Col 1-4) AK-1, AK-1-1, Title1...

Receiving 2 of every message
I recently paid for a hotmail account so I could have larger storage and not get cancelled for any reasons such as not checking it often enough. I followed the instructions and put my service on Outlook Express. Now I'm receiving 2 of every message. What's up? Can I fix this? Thanks Darla ...

page number
How do I remove page number from single pages, not only frontpage You can either insert a section break either side the un-numbered page and change the numbering in the new section or you can conditionally insert the page number in all the pages using fields e.g. {IF {Page} <> 4 "{Page}"} will insert the page number on all pages except page 4. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mv...

Convert Text to Number on 4,508 excel files.
Hi all. I'm faced with a rather unique problem.I have a large amount of improperly formatted excel files. They all have various cells formatted as text, yet they contain numbers and perform calculations on the cells. I now need to get these in a proper state. What I need is: Conversion of all text boxes containing numbers over to proper format of number. Do it automagically. I don't relish the thought of opening each one individually and fixing it. Any thoughts, tips or ideas on this? It could possible if you process that works by Scripting . In Scripting engine 5.6 Filesystem...

Help with RANK
Hi I need to rank 100+ numbers but I can't get the formula right. All th numbers are in one column, but not all the rows e.g. I need to Ran G1:G11 and G13:G25 and G32:G40 etc. How can you put in more than on reference? The cells G12,G26:G31 etc have numbers in them that I don' want ranked RANK(number,ref,order -- Message posted from http://www.ExcelForum.com =RANK(G1,MyList) where mylist is a selection of ranges defined with a single name, eg select all your ranges and do Insert / Name / Define, then call it MyList. Now use it in the rank formula and then copy the formula ...

HELP! Outlook Keeps Receiving Same E-Mail Messages
Hello: I keep getting the same 6 e-mail messages. This is after calling Norton and Comcast re receiving the same 3 e-mail messages (538 of them since midnight Dec 3!). Of course, it was not their problem, so it must be Outlook. Any thoughts. It sounds like you have an email message with a large attachment that is bogging down your account. What happens with the email is once you have connected to your ISP and clicked on the send/receive button if the connection is lost it will start over again until it can pull all of your messages in 1 sweep. You will need to log on to your accou...

Dialog getting hidden under HELP file
HI all, In my application I have a dialog & I launch HELP file from that dialog using F1 key. After that If I move the help file to one corner so that a part of the dialog is visible & click on the dialog & move it,it is moving,but when it comes over the HELP file,it is not overlapping but getting hidden under that. can anybody tell me what should I do to make it overlap the help file or give me a link that can help. Thanks in advance. Yes, that's how it is supposed to work. Help is a completely separate program, so its relationship to windows in ...