CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query
Nick K9
Nick K9

Reputation: 4674

Stacked column chart in Google Sheets taking data from multiple columns

I have a bunch of book club "star" votes in a table, broken into columns by participant. Some cells are blank where a participant didn't assign a rating.

          A  B  C
Title 1   3  4  2
Title 2   5  5  4
Title 3   2     2
Title 4   1  4  3

I want a stacked column chart (histogram?) with 5 bars, one for each possible star rating subdivided by the person making the rating. So for the above, it would look something like:


   C     C
   C  C  B  B
A  A  A  B  A
-------------
1  2  3  4  5

I cannot for the life of me figure out how to do this with Google Sheets. Both the standard Setup and "switch rows/columns" are wrong. I think maybe I need to pre-analyse the data somehow?

Upvotes: 1

Views: 975

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(
 IF(B2:D="",,"♠"&B2:D&"♦"&B1:D1),,999^99)),,999^99), "♠")), "♦")), 
 "select Col1,count(Col1) group by Col1 pivot Col2"))

0

Upvotes: 1

McKay M
McKay M

Reputation: 448

I think you'll need to count the quantity of each star type broken down by person, and organize your data like this for the table:

Star Amt A  B  C
1 Star   1  0  0
2 Star   1  0  2
3 Star   1  0  1
4 Star   0  2  1
5 Star   1  1  0

The numbers in the table represent the number of times the user (column) voted a particular star amount (row).

Maybe this video can help too.

Upvotes: 0

Related Questions