Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need help w/ Weight Formula
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Sorry,
this ain't it at all... Tom "Bob Tarburton" wrote in message ... 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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help w/ "Weight Formula" | Excel Worksheet Functions | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
weight formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |