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)
Pivot table was wrangled because of how I compute Period
. It used to be just a copy of Date
, I'd then use Sheets Data -> Number -> Format
to change how it's displayed (removing the day, leaving just month and year). This is a stupid workaround since the pivot table knows the underlying data, even if the cell doesn't display it.
Replaced this trick with =TEXT(DateCells, "yyyy/mm")
and now the data properly aggregates.
Best Answer
Here is an example:
It is very simple not only because just for illustration but also because Calculated Fields are not the place for complex formulae.