How can I assign varying points for different multiple choice questions and calculate the total score?

You can assign varying points to different multiple choice questions using the SCORE - QUESTION function.

Let's create a simple questionnaire with 3 multiple choice questions with five answer choices each (see below).

Q1. How much time do you spend sitting each day?
Watching TV, Playing computer games etc
(a) 5+ hrs
(b) 4 to 5 hrs
(c) 3 to 4 hrs
(d) 2 to 3 hrs
(e) Less than 2 hrs

Q2. How often do you do sports and recreational activities?
Playing sports, going to gym, walking, cycling etc
(a) Once or twice in a month
(b) Once a week
(c) Thrice a week
(d) 5 days in a week
(e) Daily

Q3. How much physical activity does your job require?
Carrying or lifting heavy loads, construction, digging etc
(a) Not much
(b) A moderate amount
(c) Average
(d) Intense
(e) Very intense

Points: Assign varying points for each multiple choice question
Q1: (a) - 1, (b) - 2, (c) - 3, (d) - 4, (e) - 5
Q2: (a) - 2, (b) - 4, (c) - 6, (d) - 8, (e) - 10
Q3: (a) - 5, (b) - 10, (c) - 15, (d) - 20, (e) - 25

We will calculate the total score by adding the individual points for the 3 multiple choice questions. The total score would be between 8 and 40.

How to assign different points using SCORE - QUESTION function?

Pre-requisite: You must have installed the Formfacade add-on for Google Forms. If you haven't installed the add-on yet, you can install it from G Suite marketplace using this link. https://gsuite.google.com/marketplace/app/formfacade/743872305260

SCORE - QUESTION function

Score function can be used to assign points to answer choices in multiple choice, checkboxes and grid questions and calculate the total score.

Syntax:
SCORE(scope, point-1, point-2, …, point-n)

where

  • scope - determines whether to assign points to a specific question in the form, all questions in the section, all questions in the form or only the questions that has the matching search term in the description
  • point-1, point-2, … point-n - points to be assigned for different answer choices (a), (b), (c), (d), (e) etc in sequential order

In this example, we will use the entry#id for a question as the scope to assign points for that specific question and calculate the individual score for that question.

How much time do you spend sitting each day?
(a) 5+ hrs
(b) 4 to 5 hrs
(c) 3 to 4 hrs
(d) 2 to 3 hrs
(e) Less than 2 hrs

SCORE(entry#id, 1, 2, 3, 4, 5)

  • Type @ and select the required question from the list to add its entry #id in the above formula.
  • 1, 2, 3, 4, 5 to assign points for answer choices (a), (b), (c), (d), (e) respectively.

How often do you do sports and recreational activities?
(a) Once or twice in a month
(b) Once a week
(c) Thrice a week
(d) 5 days in a week
(e) Daily

SCORE(entry#id, 2, 4, 6, 8, 10)

  • Type @ and select the required question from the list to add its entry #id in the above formula.
  • 2, 4, 6, 8, 10 to assign points for answer choices (a), (b), (c), (d), (e) respectively.

How much physical activity does your job require? Carrying or lifting heavy loads, construction, digging etc
(a) Not much
(b) A moderate amount
(c) Average
(d) Intense
(e) Very intense

SCORE(entry#id, 5, 10, 15, 20, 25)

  • Type @ and select the required question from the list to add its entry #id in the above formula.
  • 5, 10, 15, 20, 25 to assign points for answer choices (a), (b), (c), (d), (e) respectively.

To calculate the total score, simply add the three scores as shown in the screen shot below.
SCORE(entry#id, 1, 2, 3, 4, 5) + SCORE(entry#id, 2, 4, 6, 8, 10) + SCORE(entry#id, 5, 10, 15, 20, 25)