Need help w/ Weight Formula

[Tom]

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

 

[Bob Tarburton]

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

 

[Tom]

Sorry,

this ain't it at all...

Tom

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

 

[Bob Tarburton]

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,"<"&TRUNC(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.