Create weights for scoring questions and non-scoring questions using formulas in the reporting tool.
Create Weights in a Report
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.