Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Master needed - Too tough to tame
Is the below do-able in a formula in Excel 2007? Am open to suggestion/help.
I have an excel 2007 workbook with multiple tabs. On the "data" worksheet I have 50+ unique trait scores per individual. I want to take one of the scores, match to a set of ranges (on the "range" worksheet) to see where it fits. (the range magnitude may vary depending on the trait being scored - so there may be 50+ different range series - one range series for each trait) Once the trait score is matched to a range, I want to assign the trait score a specific number of points based upon the priority level that the trait has in the overall scoring of that individual. "Data tab" info would look something like this: A B C D E 1 Trait 1 Trait 2 Trait 3 Trait 4 Trait 5 2 36 9 10 17 15 "Range tab" would have 2 sets of range tables. One to determine the range a trait score fits to (sort of like in school a score of 81 fits to the range of 78-84 which is a C), the other range table is where the points assigned to the trait score are determined, based upon the "level of importance" that the trait itself carried. The ranges a score would fit into would look something like this: Importance Range 1 Range 2 Range 3 Range 4 Level From To From To From To From To Trait 1 1 0 14 15 25 26 33 34 50 Trait 2 2 0 10 11 20 21 40 41 55 Trait 3 3 5 15 16 22 23 43 44 70 The points chart would look like this: Importance Range 1 Range 2 Range 3 Range 4 Level Points Points Points Points 3 10 5 3 1 2 5 3 2 1 1 3 2 1 0 So then, if I wanted to get a "points score" for Trait 1 with a Trait Score of 36 on the "data" tab, I would see that on the "range tab" it fit in Range 4 (34-50) and since the Importance Level was a 1, it would return a value of 1 (point). Trait 2 with a Trait Score of 9 on the "data" tab would fit in Range 1 (0-10) and with an Importance Level of 2 it would return a value of 5 (points). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Master needed - Too tough to tame
I tried the setup below;
In Data tab entered B1=Trait1, B2=Trait2 and so on in row 2 starting at B2 entered trait scores for the individual in A1 in B3 entered =MATCH(B1,Range!$A:$A,0) to get the row no for trait in B1 on Range tab in B4 entered =INT((MATCH(B2,INDIRECT("Range!"&B3&":"&B3),1)-1)/2) to get the range the score falls in in B5 entered =INDEX(Range!$A$1:$J$100,B3,2) to get the importance for the trait in B6 entered =VLOOKUP(B5,Value!$A:$E,Data!B4+1,FALSE) to get the final result I named the tab, which contains the final value for the given importance and range, as Value You can merge the formulas once you test them out and understand the logic... If you copy it to the right, it will work for different traits and scores... You need to take care of refrences if you copy it down... btw answer should be 0 for trait 1, range 4, importance 1... "RiverCity" wrote: Is the below do-able in a formula in Excel 2007? Am open to suggestion/help. I have an excel 2007 workbook with multiple tabs. On the "data" worksheet I have 50+ unique trait scores per individual. I want to take one of the scores, match to a set of ranges (on the "range" worksheet) to see where it fits. (the range magnitude may vary depending on the trait being scored - so there may be 50+ different range series - one range series for each trait) Once the trait score is matched to a range, I want to assign the trait score a specific number of points based upon the priority level that the trait has in the overall scoring of that individual. "Data tab" info would look something like this: A B C D E 1 Trait 1 Trait 2 Trait 3 Trait 4 Trait 5 2 36 9 10 17 15 "Range tab" would have 2 sets of range tables. One to determine the range a trait score fits to (sort of like in school a score of 81 fits to the range of 78-84 which is a C), the other range table is where the points assigned to the trait score are determined, based upon the "level of importance" that the trait itself carried. The ranges a score would fit into would look something like this: Importance Range 1 Range 2 Range 3 Range 4 Level From To From To From To From To Trait 1 1 0 14 15 25 26 33 34 50 Trait 2 2 0 10 11 20 21 40 41 55 Trait 3 3 5 15 16 22 23 43 44 70 The points chart would look like this: Importance Range 1 Range 2 Range 3 Range 4 Level Points Points Points Points 3 10 5 3 1 2 5 3 2 1 1 3 2 1 0 So then, if I wanted to get a "points score" for Trait 1 with a Trait Score of 36 on the "data" tab, I would see that on the "range tab" it fit in Range 4 (34-50) and since the Importance Level was a 1, it would return a value of 1 (point). Trait 2 with a Trait Score of 9 on the "data" tab would fit in Range 1 (0-10) and with an Importance Level of 2 it would return a value of 5 (points). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Master needed - Too tough to tame
Sheeloo, this is your world, I'm just glad you let me live in it. Spot-on
was your work. I struggled with this Friday thru Monday morning, coming up with a much more complex and balky solution. In a matter of minutes you constructed the answer - perfectly. There was nothing on the web that remotely resembled this issue in this format. Much, much thanks and appreciation. I owe you one. "Sheeloo" wrote: I tried the setup below; In Data tab entered B1=Trait1, B2=Trait2 and so on in row 2 starting at B2 entered trait scores for the individual in A1 in B3 entered =MATCH(B1,Range!$A:$A,0) to get the row no for trait in B1 on Range tab in B4 entered =INT((MATCH(B2,INDIRECT("Range!"&B3&":"&B3),1)-1)/2) to get the range the score falls in in B5 entered =INDEX(Range!$A$1:$J$100,B3,2) to get the importance for the trait in B6 entered =VLOOKUP(B5,Value!$A:$E,Data!B4+1,FALSE) to get the final result I named the tab, which contains the final value for the given importance and range, as Value You can merge the formulas once you test them out and understand the logic... If you copy it to the right, it will work for different traits and scores... You need to take care of refrences if you copy it down... btw answer should be 0 for trait 1, range 4, importance 1... "RiverCity" wrote: Is the below do-able in a formula in Excel 2007? Am open to suggestion/help. I have an excel 2007 workbook with multiple tabs. On the "data" worksheet I have 50+ unique trait scores per individual. I want to take one of the scores, match to a set of ranges (on the "range" worksheet) to see where it fits. (the range magnitude may vary depending on the trait being scored - so there may be 50+ different range series - one range series for each trait) Once the trait score is matched to a range, I want to assign the trait score a specific number of points based upon the priority level that the trait has in the overall scoring of that individual. "Data tab" info would look something like this: A B C D E 1 Trait 1 Trait 2 Trait 3 Trait 4 Trait 5 2 36 9 10 17 15 "Range tab" would have 2 sets of range tables. One to determine the range a trait score fits to (sort of like in school a score of 81 fits to the range of 78-84 which is a C), the other range table is where the points assigned to the trait score are determined, based upon the "level of importance" that the trait itself carried. The ranges a score would fit into would look something like this: Importance Range 1 Range 2 Range 3 Range 4 Level From To From To From To From To Trait 1 1 0 14 15 25 26 33 34 50 Trait 2 2 0 10 11 20 21 40 41 55 Trait 3 3 5 15 16 22 23 43 44 70 The points chart would look like this: Importance Range 1 Range 2 Range 3 Range 4 Level Points Points Points Points 3 10 5 3 1 2 5 3 2 1 1 3 2 1 0 So then, if I wanted to get a "points score" for Trait 1 with a Trait Score of 36 on the "data" tab, I would see that on the "range tab" it fit in Range 4 (34-50) and since the Importance Level was a 1, it would return a value of 1 (point). Trait 2 with a Trait Score of 9 on the "data" tab would fit in Range 1 (0-10) and with an Importance Level of 2 it would return a value of 5 (points). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to Tame the "formula" beast, PLEASE? | Excel Worksheet Functions | |||
Tough one | Excel Discussion (Misc queries) | |||
OK tough one ! | Excel Worksheet Functions | |||
Pleeze Help! Export Excel data into a form in a Word Doc...this one's tough! | Excel Discussion (Misc queries) | |||
Simple for you, tough for me, basic excel question | Excel Discussion (Misc queries) |