R
RiverCity
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.
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.