Google sheets db query assistance.

This should be a quick 1-2 hour job for anyone experienced with Google sheets query.

I have a sheet full of survey results, sometimes people will perform the survey twice, and I want to make a query that compares the variance of 1st survey vs 2nd survey for the same person. The survey will have 8-10 questions, each question is answered with a 1-5 scale.

I have made a named range of the whole sheet called SurveyResults.

I would like to make a report that outputs the variance (VAR query) of each question if a person answered it twice, and maybe answered differently the 2nd time. Sample:
Columns: A,B,C,D,E,F

Sample data, answer for question 1 found in Column B and name in column E
3 jane
5 joe
2 joe
5 jane

In this case Jane answer question1 (Q1), 2 times, so I see variance of of +2 from 3 vs 5.

Joe answered Q1 twice, and there is a variance of -3.

Here is the sheet:
https://docs.google.com/spreadsheets/d/1n9344zyCBL1QD9vZLCUYKRn46RXejLXqw9gdRyhSUGc

I have gotten this far:

=QUERY(SurveyResults,SELECT B, ..... something WHERE E is the same, VAR..., -1).

There are a couple other queries I want to make, but that is the starting point.

For instance, Column F may have the company name of a person, and I want to calculate the average variance for each question from everyone in the same company. There may be additional needs in the future for this.
About the recuiter
Member since Mar 14, 2020
Muh Imran
from Dodoma, Tanzania

---

---

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions