There are several possible approaches for creating weighted evaluation questions. Weights can be created on the evaluation form itself through how you design the scoring questions, or weights can be applied using the reporting tool.
Create Weights on the Evaluation Form
Create weighted scoring questions on the evaluation form, eliminating the need to use formulas in the reporting tool.
Weighted Questions Without Specific Values
If certain scoring questions need to carry more weight than others but do not need to carry a specific value, the approach described below is appropriate. For example, question one needs to carry 1.5 times the weight of questions two and three.
When adding scoring questions on the evaluation form, make the maximum possible points higher for the questions that should carry more weight. The score for those questions has a stronger impact on the overall score as those questions account for a larger portion of the total points possible.
Example
In the image below, each scoring question has the same maximum possible points. No weights are applied. The system calculates the evaluation score by dividing the total points scored by the total points possible, so the score below is 23/30 or 77%.
In the image below, the first scoring question has been given higher maximum possible points. This weights that question more heavily than the others. The evaluation score is now 23/35 or 66%. The score for the first question has a larger impact on the overall evaluation score as that question now accounts for a larger portion of the total possible points.
Weighted Questions With Specific Values
If each scoring question needs to carry a specific value of weight, the approach described below is appropriate. For example, question one needs to be worth 25% of the total score, question two 25%, and question three 50%.
- Determine the maximum total points possible for the evaluation.
- Determine the percent of the total that each question should carry.
- Multiply each percent by the total points possible to determine the maximum possible points for each question.
Example
The maximum total points possible are set at 20. The maximum possible points for each question is calculated as follows:
- Question one needs to be worth 25% of the total score: .25 x 20 = 5.
- Question two needs to be worth 25% of the total score: .25 x 20 = 5.
- Question three needs to be worth 50% of the total score: .50 x 20 = 10.
These three scoring questions are then built on the evaluation form as shown below.
Create Weights in a Report
Create weights for scoring questions and non-scoring questions using formulas in the reporting tool.
Assign a Percent Value to Scoring Questions
If each scoring question needs to carry a specific value of weight and the questions each have the same maximum possible points on the evaluation form, the approach described below is appropriate. For example, question one needs to be worth 25% of the total score, question two 25%, and question three 50%.
- Prepare the evaluation report according to instructions in Build an Evaluation Report.
- The following instructions involve the use of formulas. Add a Formula to a Report provides foundational information to reference as needed.
- On the report, click Formula.
- Enter a Name for the new column of data that will display the total weighted scores.
- In the Formula text box, enter a pair of parentheses, and then place your cursor within the parentheses.
- Select the first scoring question from the Insert a Column drop-down menu.
- In the Formula text box and inside the parentheses, enter the following:
- An asterisk to tell the formula to multiply.
- The percent by which the question should be multiplied (e.g. .25 for 25%).
- A plus sign after the parentheses to tell the formula to add the result to the next part of the formula.
- Repeat steps 4-6 for each scoring question.
- When this is complete, the formula should resemble the one pictured below.
- When this is complete, the formula should resemble the one pictured below.
- At this point, the formula will calculate the total weighted scores in points. To display the weighted score as a percent, the remaining steps are necessary. Complete the formula as described below.
- Determine the total possible weighted score. For each question, multiply the total points possible for the question by the weighting percent (e.g. 10 x .25 = 2.5). Add all results to find the total.
- Add parentheses around the entire formula.
- After the final closing parentheses, add a forward slash and the total possible weighted score.
- Complete the remaining applicable fields, and then click Add.
- Data Type - Select Number.
- Display Format - Select Percent.
The total weighted score for each evaluation now displays as a percent in the new column.
Assign Points to Responses
If specific points should be assigned based on the response to a non-scoring question, the approach described below is appropriate. This could refer to responses to an application question or a non-scoring question on the evaluation form. For example, a question about geographic area served could have different points assigned based on the response: 10 for West, 5 for South, 20 for Midwest, or 15 for Northeast.
- Prepare the evaluation report according to instructions in Build an Evaluation Report.
- The following instructions involve the use of formulas. Add a Formula to a Report provides foundational information to reference as needed.
- On the report, click Formula.
- Enter a Name for the new column of data that will display the weighted scores for the question.
- Select IIF(expression,true-value,false-value) in the Formula drop-down menu.
- In the Formula text box, highlight "expression."
- Select the applicable question in the Insert a Column drop-down menu.
- After the column name and before the comma, enter the first possible response in quotations.
- Replace "true-value" with the value of the first response.
- Highlight "false-value" and select IIF(expression,true-value,false-value) in the Formula drop-down menu.
- This creates another IIF statement nested within the first, telling the reporting tool that if the response is not the first option, look at the second option. The formula should then resemble the one below.
- This creates another IIF statement nested within the first, telling the reporting tool that if the response is not the first option, look at the second option. The formula should then resemble the one below.
- Fill in the information for the second response and value in this new IIF statement.
- Highlight "expression" and select the applicable question in the Insert a Column drop-down menu.
- Enter the second possible response and its value to replace the "true-value" placeholder text.
- If a third response is possible, repeat steps 9-10 to add another nested IIF statement. Continue doing so for all the possible responses.
- Replace the final "false-value" text with 0. This indicates that if none of the possible responses are found, the report should display 0 in that cell.
- Complete the remaining applicable fields, and then click Add.
- Data Type - Select Number.
- Display Format - Select "#,##0.#".
The weighted scores for the question now display in the new column.