Here’s my Excel problem:
There are 5 organizations within the company.
Each organization has been asked to evaluate each of some 60 systems for each year 2004-2008 (5 years).
The evaluation is composed of 258 questions to which the rating organization responds with a score of 1,3,5,7,9, or NA.
So, at the simplest (I use this term loosely) level, I have a 5-column, 258-row sheet containing the responses. The column headings are the organization names (let’s say Red, Grn, Yel, Blu, and Tan) while the rows are the 258 questions.
However, each organization has a different weight assigned to it based on its importance in the evaluation of any particular system for any particular year. The combination (roll-up) of these weighted scores then constitutes the “company” evaluation.
In my example below, ideally Red is weighted as 50% of the total (or 0.5), Grn and Yel each weighted as 5% of the total (or 0.05), and Blu and Tan each weighted as 20% (or 0.20) of the total. I have displayed these weights on row 2.
So, to get my “roll-up” score in “A4” for the scores posted as shown in row 4, the formula is: “B4*B2+C4*C2+D4*D2+E4*E2+F4*F2” which yields 4.5+.25+.35+1.4+1 or 7.5. No problem yet.
However, if an organization chooses NOT to evaluate and responds NA to a question, the relative weightings must be recalculated to maintain the same relative value for each organization that does respond. So, in row 5, Blu has responded “NA.” This means that the “roll-up” is now composed of only 4 organizations whose total weight is only .8 based on the original weightings. I need to convert the weightings so that each responding organization’s answer is valued in the same proportion as originally determined. So, in the case of Red for row 5, its new weight is 0.5/0.8 or 0.625, Grn is 0.05/0.8 or 0.0625, Yel is 0.05/0.8 or 0.0625, and Tan is 0.2/0.8 or 0.25. So, the formula in “A5” to determine the roll-up of the four organizations responding is: “B5*0.625+C5*0.0625+D5*0.0625+F5*0.25 which yields 5.625+0.3125+0.3125+1.25 or 7.5. (Had I done it using the original weights I would have gotten 6.1!) The number responding organizations will vary from 5 to 1.
I need to have a formula that:
a. Looks at each row and determines which orgs are not responding.
b. Recalculates the appropriate weightings for each row as described above.
c. Completes the formula using the new weights to arrive at the correct value in the appropriate row cell in column A.
d. Repeats the process for each succeeding row until the range of 286 rows is complete.
Note: There are 61 worksheets, each with 258 questions to which this process must be applied.
A B C D E F
1 Red Grn Yel Blu Tan
2 .50 .05 .05 .20 .20
3
4 7.5 9 5 7 7 5
5 7.5 9 5 7 NA 5
6 7 7 NA 5 NA
7 5 NA 5 7 7
8 3 5 5 5 NA
9 5 NA NA 9 NA
etc.
There are 5 organizations within the company.
Each organization has been asked to evaluate each of some 60 systems for each year 2004-2008 (5 years).
The evaluation is composed of 258 questions to which the rating organization responds with a score of 1,3,5,7,9, or NA.
So, at the simplest (I use this term loosely) level, I have a 5-column, 258-row sheet containing the responses. The column headings are the organization names (let’s say Red, Grn, Yel, Blu, and Tan) while the rows are the 258 questions.
However, each organization has a different weight assigned to it based on its importance in the evaluation of any particular system for any particular year. The combination (roll-up) of these weighted scores then constitutes the “company” evaluation.
In my example below, ideally Red is weighted as 50% of the total (or 0.5), Grn and Yel each weighted as 5% of the total (or 0.05), and Blu and Tan each weighted as 20% (or 0.20) of the total. I have displayed these weights on row 2.
So, to get my “roll-up” score in “A4” for the scores posted as shown in row 4, the formula is: “B4*B2+C4*C2+D4*D2+E4*E2+F4*F2” which yields 4.5+.25+.35+1.4+1 or 7.5. No problem yet.
However, if an organization chooses NOT to evaluate and responds NA to a question, the relative weightings must be recalculated to maintain the same relative value for each organization that does respond. So, in row 5, Blu has responded “NA.” This means that the “roll-up” is now composed of only 4 organizations whose total weight is only .8 based on the original weightings. I need to convert the weightings so that each responding organization’s answer is valued in the same proportion as originally determined. So, in the case of Red for row 5, its new weight is 0.5/0.8 or 0.625, Grn is 0.05/0.8 or 0.0625, Yel is 0.05/0.8 or 0.0625, and Tan is 0.2/0.8 or 0.25. So, the formula in “A5” to determine the roll-up of the four organizations responding is: “B5*0.625+C5*0.0625+D5*0.0625+F5*0.25 which yields 5.625+0.3125+0.3125+1.25 or 7.5. (Had I done it using the original weights I would have gotten 6.1!) The number responding organizations will vary from 5 to 1.
I need to have a formula that:
a. Looks at each row and determines which orgs are not responding.
b. Recalculates the appropriate weightings for each row as described above.
c. Completes the formula using the new weights to arrive at the correct value in the appropriate row cell in column A.
d. Repeats the process for each succeeding row until the range of 286 rows is complete.
Note: There are 61 worksheets, each with 258 questions to which this process must be applied.
A B C D E F
1 Red Grn Yel Blu Tan
2 .50 .05 .05 .20 .20
3
4 7.5 9 5 7 7 5
5 7.5 9 5 7 NA 5
6 7 7 NA 5 NA
7 5 NA 5 7 7
8 3 5 5 5 NA
9 5 NA NA 9 NA
etc.
