It sounds (at least from this descrition) as if the lower and upper
limits are somewhat arbitrary, but I think you're looking for
something like the table below, wheer you copy D2:E2 down. I leave it
to you to set the lower and upper limits. Note that the randbetween
function will change every time you recalculate. Paste values if you
want cosistency in repeated tests.


A B C D E
your# lower upper random weight
1.01 900 1000 =RANDBETWEEN(b2,c2) =D2/SUM(D$2:D$22)
1.02
1.03
1.04
1.05
1.06
1.07
1.08
1.09
1.10
1.11
1.12
2.01
2.02
2.03
2.04
3.01
3.02
4.01
4.02
4.03 1 50 =RANDBETWEEN(b22,c22) =D22/SUM(D$2:D$22)


On Wed, 2 Mar 2005 20:09:30 -0500, "Tom" wrote:

I need some help with developing some function... here what I need to
achieve:

Create "weights" for the 21 numbers listed below (in between ***s).

Their cumulative weight (sum) must equal 100% (or 1).

Here's the trick though... the weight of the 21 listed numbers below are NOT
sequential.

For instance:

1.01 may equal = "0.2800"
1.02 may equal = "0.2200"
1.03 may equal = "0.1600"

but now,
2.01 may equal = "0.1000"
3.01 may equal = "0.0800"
4.01 may equal = "0.0500"

then

1.04 may equal = "0.0250"
1.05 may...
2.02 may...
3.02 may...
4.02 may...
1.06 may...
etc.

Essentially, it would look this the following:

Group 1 High
Group 2 High
Group 3 High
Group 4 High
Group 1 Medium
Group 1 Low
Group 2 Low
Group 3 Low
Group 4 Low


Also, there should be some relationship between the propitiate counts within
each of the 4 groups.

Group 1 contains: 12 records
Group 2 contains: 4 records
Group 3 contains: 2 records
Group 4 contains: 3 records

Again, the top numbers (e.g. "1.01 to 1.03") should get the greatest
weights.
But the top number of the other 3 groups (2.01, 3.01, 4.01) must be greater
than the lower numbers of Group 1 (1.04 to 1.12).

At the same time, the lower numbers of Group 2 - 4 (e.g. 2.02 to 2.04; 3.02;
4.2 to 4.3) must be lower than the lower numbers of Group 1 (1.04 to 1.12).

Group 1 is the only group with a relative high count of numbers (compared to
the other 3 groups). So, maybe Group 1 should have something like "High
Numbers", "Medium Numbers", and "Low Numbers"... while the other 3 groups
have only "High and Low Numbers".

Lastly, this needs to be somehow dynamic... I may add or delete to/from
these 21 numbers. So, the function that calculates the "propitiate counts"
amongst the 4 groups must be automatically adjusted based on additions or
deletions.

This does not have to be 100% scientific-proof... nevertheless, I'd like
this to be as "scientific" as it can be.


****
1.01
1.02
1.03
1.04
1.05
1.06
1.07
1.08
1.09
1.10
1.11
1.12
2.01
2.02
2.03
2.04
3.01
3.02
4.01
4.02
4.03
****

Thanks for any ideas,
Tom