generate random numbers from a frequency list

**adeina** · 06-21-2007, 09:07 AM

Hi everyone,
I have a frequency list (frequency per year) resulted from previous calculation. What I want is to generate random number as many as the frequency in the specified year, so that every year I will get the same amount of random numbers as the frequency. It is kind of the opposite of FREQUENCY function (but with random numbers involved). I need to use the result for further calculation.
Could somebody help me to solve this problem??Thanks in advance!!

**oldchippy** · 06-21-2007, 09:18 AM

Try the RANDBETWEEN function

**adeina** · 06-21-2007, 09:25 AM

What I want is to have random number automatically generated as many as a specified number (frequency per year). For example, if I have 3 frequency for 2007, then I want to have 3 random numbers in an array. The frequency list is dynamic and based on the previous calculation.

**oldchippy** · 06-21-2007, 09:31 AM

With your number is say A1, then this should work

=RANDBETWEEN(1,A1)

**adeina** · 06-21-2007, 09:56 AM

Hi oldchippy,
I think we talk about different things...
I do not want to specify the range of my random numbers. RANDBETWEEN will generate one random number in a specified range.

I want to have one or more random numbers (or no random number if the frequency is 0) as specified by a list of frequency. As I mentioned before, if I have 3 frequency, then I want to have an array of RandomNumber1, RandomNumber2, RandomNumber 3. If the next year I have 5 for my frequency, then I want to have 5 random numbers: RandomNumber1, RandomNumber2, RandomNumber, RandomNumber4, RandomNumber 5.

Year Freq.
2007 3 RN1 RN2 RN3
2008 5 RN1 RN2 RN3 RN4 RN5

I really hope that I make it clear now...

**oldchippy** · 06-21-2007, 10:10 AM

Hello adeina,

Sorry trying to do to many things at once, didn't read the question properly. Perfectly clear what you want now, but there is no Excel function that does that, you need one of the VBA experts to help here. That is way beyond anything I can do - sorry.

**adeina** · 06-21-2007, 10:44 AM

No problem, oldchippy.
So I guess I really have to use VBA for this...
Somebody to help me please??

**BigBas** · 06-21-2007, 10:54 AM

Does the random number have to be within any range? Any min? Max?

**adeina** · 06-21-2007, 10:56 AM

Yes, it should be within specified range.

**BigBas** · 06-21-2007, 11:00 AM

One workaround that I have built, without using VBA, is a somewhat roundabout formula. Check it out, and let me know what you think.

Lets assume the frequency is in A1. Starting in B1, enter this formula, and fill it across as far as necessary:

=INT((RAND()*100))*IF(COLUMN(A1)<=$A1,1,0)

It will return a 0 value for all cells beyond the number specified. If necessary, the formula can be nested to return a blank instead of 0.

EDIT TO ADD: I just saw your statement that it must be within a range. How is that range defined?