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:
Sample data, answer for question 1 found in Column B and name in column E
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.