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/1n9344zyCBL1QD9vZLCUYKRn46RXejLXqw9gdRyhSUGcI 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 recuiterMember since Mar 14, 2020 Muh Imran
from Dodoma, Tanzania