Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find range a value fits to, assign new value based on importance
I have an excel 2007 workbook with multiple tabs. On the "data" worksht 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). I can make this work with a gigantic IF, AND formula but I know there is some way to greatly shorten it. Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I would like a function to return a value that fits its range | Excel Worksheet Functions | |||
Find category value based on date range? | Excel Worksheet Functions | |||
How can I assign a range starting cell based on a variable locati. | Excel Discussion (Misc queries) | |||
How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions |