1

I have a Google Survey/Spreadsheet. It simply collects some data on therapists we have and the patient satisfaction. I want to make a pivot table that summarizes this data so we can compare the therapists. I put the therapist name as the row and nothing as the column. Then, I put in a few values: count of timestamp (i.e., count how many surveys for each therapist).

But I need some custom calculations and I don't know how to do them. For example, two survey questions are "have you had a massage" and "was this massage better than prior ones". I want to calculate, for each therapist, the % of their patients that said they had a better massage. I can calculate, for each therapist, the number that said they had a prior massage and the number that said it was better. But then I have to manually create another column that divides the two to calculate a percentage, and its just the percentage I care about. It's messy.

  • It might be useful for you to include some sample data in your question to illustrate what you're after. – ale Dec 12 '15 at 15:40
0

As a pre-processing step, I suggest representing "yes" answers as 1 and "no" as 0; this makes aggregation easier.

Then in a pivot table, you can simply create a calculated field =sum(better)/sum(had), assuming "had" and "better" are your column headers.


However, I don't see the need for pivot table report when you are essentially using it as a query. Sorting within a pivot table report can be problematic, and in any case doesn't happen automatically. Here is a query solution, again assuming that the responses are recorded as 0-1 numbers. It automatically sorts by ratio (better)/(had prior), in descending order.

=query(A:C, "select A, sum(C)/sum(B) group by A order by sum(C)/sum(B) desc", 1)

The last argument makes it explicit that the input data hasa= a header row.

And here is a more elaborate version, with labels on columns.

=query(A:C, "select A, sum(C)/sum(B) group by A order by sum(C)/sum(B) desc label A 'Name', sum(C)/sum(B) 'Ratio'", 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.