Random number to a specific total within a Range

**lavan_joy** · 08-13-2009, 06:37 AM

I want to create random numbers for a specific total within a range.

I'm a newbie to Excel. So if anyone can create and attach the excel file it will be more helpful.

The upper limit,lower limit, No of random numbers & Total has to be entered manually and the excel sheet has to provide 31 random numbers.

For example:
I want to create random numbers for 40,000 within a range of 250 to 2,000

Lower limit = 250
Upper limit =2,000
Random numbers Total will be 40,000
No. of random numbers =31
Duplicate Randoms: Allowed

1864
1800
338
780
1304
1600
1281
1239
1700
878
1139
1390
1750
540
1839
1460
750
828
1925
1724
1822
1353
1769
1950
1952
1603
1423
1999
---------------
Total =40,000
---------------

Thanks a lot.

**Cheeky Charlie** · 08-13-2009, 06:53 AM

Without trying to sound like a pedant - you dan not define random numbers to come to a given total - numbers which come to a pre-specified total are not random.

Perhaps you could expand and we could look at which stage we need to replace "random" with "calculated"

CC

**lavan_joy** · 08-13-2009, 06:58 AM

Thanks for the reply. I updated my post. Please check that.

**Cheeky Charlie** · 08-13-2009, 07:00 AM

Well... whether the total is 40,000 or 50,000, my point remains.

**zbor** · 08-13-2009, 07:01 AM

Like this:

Book1.xls

Right now totaln number are 10.
Write whatever you like only don't forget to extend formula down in random number column

**lavan_joy** · 08-13-2009, 07:02 AM

40,000 or 50,000. or any Number. I want to change the total manually everytime.

**lavan_joy** · 08-13-2009, 07:10 AM

Originally Posted by Cheeky Charlie

Well... whether the total is 40,000 or 50,000, my point remains.

40,000 or 50,000 or any particular number. I want to enter the total manually and need random numbers.

Originally Posted by zbor

Like this:

Attachment 50680

Right now totaln number are 10.
Write whatever you like only don't forget to extend formula down in random number column

Yes. I need like this. I need one more addition. The total of random numbers should be equal to the manually entered total. If I enter 40,000, then then the total of random numbers will be 40,000.

**zbor** · 08-13-2009, 07:16 AM

Yes, it is. Only extend formula down.

**martindwilson** · 08-13-2009, 07:46 AM

i see cc's point. if you want random numbers to = a given total like that
say you rand between 1-10 and you want the total 10 from 6 random numbers
its possible that any generated 6 numbers will <>10 or come to that any 10 numbers wont = 10 say 5,7,4,9,8,8,4,4,7,9

**Excel Matic** · 08-13-2009, 08:10 AM

But the point still remains that the total should equal to a given specific number!! and thats not the case in the given solution

**martindwilson** · 08-13-2009, 08:15 AM

so basically you want cells to keep recalculating until a given total is reached
that might never happen or just take ages to do

**Excel Matic** · 08-13-2009, 08:19 AM

Exactly, that is what my point is. It is not possible to do this. Any other way which can help solve this would probably would need to write macros

**lavan_joy** · 08-13-2009, 08:42 AM

Originally Posted by martindwilson

so basically you want cells to keep recalculating until a given total is reached
that might never happen or just take ages to do

I agree with you. If anyone find a way, please let us know.

Can anyone make like this? The total of random number should be any closer number to the input total.
Input total =40,000

The random number's total which is closer to 40,000 is enough. For Example 39,250 or 40,850

**martindwilson** · 08-13-2009, 08:43 AM

well i thought ah! solver but that is limited to 200 changes so i did it with 200 rand numbers and the nearest was 42000ish but its a bit buggy given unexpected results im off to try it on another version of excel
hm when its that big it wont rcognise binary restraints!!!!!!!!

**Cheeky Charlie** · 08-13-2009, 08:48 AM

Originally Posted by Excel Matic

Exactly, that is what my point is. It is not possible to do this. Any other way which can help solve this would probably would need to write macros

I believe that was my point...

We could easily make a set of random numbers which come to a predefined total (each random number * desired total / (sum of random numbers))

But the beginning and end point would not be fixed...

We could also make a set of random numbers with fixed beginning and end points (random number * (max + min) ) - min
but this would not have a predefined total...

You have to choose, OP, to give up specifying either:
your beginning and end points,
the total sum, or:
the total number of random numbers

CC

**lavan_joy** · 08-13-2009, 08:53 AM

Edited:
I don't know what to do!!!

If anyone find a way (if possible) let us know about that. Thanks for all of your help guys.

**Cheeky Charlie** · 08-13-2009, 09:14 AM

Here,

Beginning and end points fixed, total fixed(ish) total number of random numbers varies. As per request before editing post!

HTH

**lavan_joy** · 08-13-2009, 09:24 AM

Thanks Cheeky Charlie! Very helpful one.

**martindwilson** · 08-13-2009, 09:36 AM

now that was simple cc!!! so obvious, wood for trees me

Random number to a specific total within a Range

LinkBack

Thread Tools

Rate This Thread

Display

Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Re: Random number to a specific total within a Range

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions