Google Sheets – Create a Pivot Table with Custom Formula Per Row

google sheets

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.

Best Answer

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)
Related Topic