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
|