Upon carefully re-reading your request I'll take another shot.

Sounds like you want to
1. add a value for the integer portion of your number with 1 receiving
the greatest value, 4 receiving the least something like
=10-LEFT(A1,1)

2. sutract a value for the integer portion, with integers .04 and
greater getting a disproportionately high subtraction something like
-MID(A2,2,3)*IF(--MID(A2,2,3)<0.04,lower_value,higher_value)

3. add a value for the number of entries in your group, like
+(COUNTIF(A$1:A$21,"<"&TRUNC(A2)+1)-COUNTIF(A$1:A$21,"<"&TRUNC(A2)))

(All assuming your numbers are in A1:A21)

B1
=10-LEFT(A1,1)/2-MID(A1,2,3)*IF(--MID(A1,2,3)<0.04,10,70)+(COUNTIF(A$1:A$21,"<"&TRUN C(A1)+1)-COUNTIF(A$1:A$21,"<"&TRUNC(A1)))/12
Copy down
Ranks the numbers within the parameters you listed (If I read them
correctly)

C1 =B1/SUM(B$1:B$21)
Copy down
Changes the ranks to weights.

You can fool with the "/2" "10,70" and "/12" to fine tune the
formula. Maybe embed further IF statements if you want more seperation
than high and low on the decimal.



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