How to create a form that will give a weighted score

Shandreka Davenport 60 Reputation points
2026-06-09T16:25:05.3133333+00:00

I need to create a form that will give me a weighted score. I've tried to use Microsoft Forms.

Example of the questions

User's image

Scale

User's image

Scoring

User's image

Microsoft 365 and Office | Microsoft Forms | Other
0 comments
Answer accepted by question author
Kai-H 27,310 Reputation points Microsoft External Staff Moderator
2026-06-11T13:51:45.5166667+00:00

Hi, Shandreka Davenport

Microsoft Forms puts each response across one row, so if your scoring table is laid out horizontally, use a horizontal lookup formula rather than trying to turn the whole Forms sheet around. Excel also has TRANSPOSE if you simply need to display a row as a column.

Here are some suggestions you can try:

If your scale is set up like this:

A1:E1 = No Income | Income is insufficient | Meets some needs | ...

A2:E2 = 1 | 2 | 3 | 4 | 5

and the Microsoft Forms answer is in B2, use:

=HLOOKUP(B2,$A$1:$E$2,2,FALSE)

That tells Excel to find the answer text across the top row, then return the number from the row underneath. The FALSE part means it must match the wording exactly.

If you only need to turn a horizontal row into a vertical list, use:

=TRANSPOSE(B2:Z2)

In Microsoft 365, enter it once and Excel should spill the results down automatically. In older Excel versions, select the destination cells first, type the formula, then press Ctrl + Shift + Enter.

For weighted scoring, you can then multiply the returned number by that question’s weight, for example:

=HLOOKUP(B2,$A$1:$E$2,2,FALSE)*3

Just make sure the answer text in the scale table matches the Forms answer exactly, including spaces and punctuation.

Thank you for your patience in reading, I hope this information has been helpful to you. 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment."    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Was this answer helpful?

1 person found this answer helpful.
Answer accepted by question author

AI answer

2026-06-09T16:25:20.71+00:00

Microsoft Forms does not support assigning hidden numeric “backend values” to options or doing weighted scoring and banding directly in the form UI. It can, however, collect the responses so that the scoring can be calculated in Excel.

A workable approach is:

  1. Create the form in Microsoft Forms
    • Go to Microsoft Forms and select New Form.
    • Add each question as a Choice question with the text options (for example, “No Income”, “Income is insufficient…”, etc.).
    • Repeat for all questions that should contribute to the score.
  2. Open the responses in Excel
    • After the form is created, use Open in Excel (or create the form from Forms for Excel in OneDrive/Excel so the workbook is created automatically). This stores each response as a row in an Excel table.
  3. Map options to numeric scores in Excel
    • In the Excel workbook, add helper columns for each question.
    • Use formulas (for example, IF, IFS, or VLOOKUP/XLOOKUP) to convert each text answer into the numeric value (1–5 in the example).
    • If some questions are weighted more heavily, multiply the numeric value by the desired weight in the helper column.
  4. Calculate total and percentage scores
    • Add a column that sums the weighted scores across all questions for each respondent.
    • Add another column that divides by the maximum possible score (e.g., =TotalScore / MaxPossibleScore * 100) to get the percentage.
  5. Classify into bands
    • Add a final column that uses the percentage (or raw score) to return the band text, for example:
      • =IFS(Score<20,"Crisis",Score<40,"Vulnerable",Score<60,"Safe",Score<80,"Building Capacity",TRUE,"Empowered").

This keeps the form simple for respondents while Excel handles the weighted scoring, total score, percentage, and category.

References:

AI-generated content may be incorrect. Read our transparency notes for more information.

Was this answer helpful?

1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shandreka Davenport 60 Reputation points
    2026-06-09T18:49:38.4833333+00:00

    The excel sheet comes over in horizontal rows what formula can be used to look up from horizontal to vertical?

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.