Charts to show field averages
I have a survey - there are 10 questions that are ratings - symbols 5 stars.
I need to create a graph that shows the weekly average rating for each question - prefer a bar chart.
I've created a report to filter the data for the last week. I have a dashboard and see the chart widget. However, I can't figure out where to use the AVG formula.... is that another report or sheet I need to create?
Thanks!
Answers
-
I do this by creating a separate sheet. That sheet has one column per star level and a row for the date period. Then a COUNTIFS formula with cross sheet references is used to pull the counts of each star level in each time period. In my case the period is years not weeks. I redacted the sheet name, but it looks like this:
=COUNTIFS({Reviews Rating}, 3, {Reviews Year}, Year@row)
Then to find the average score I multiply the count of each rating by the rating itself and divide that by the number of ratings to find the average.
=(([5]@row * 5) + ([4]@row * 4) + ([3]@row * 3)) / COUNTIF({Reviews Year}, Year@row)
Then you have two columns to use in your graph (I actually have another hidden column between 5 and Average for year, which is a repeat of the year as the graph needs the two columns to be next to each other, but I also want year on the left as I use the sheet for other things as well).
-
Ok, I think I'm tracking - so I'd need one sheet like this for each of the 10 questions, correct?
Then, somehow I bring the averages for all 10 questions into one bar graph. I'm envisioning one chart - the horizontal axis are the 10 questions and the vertical axis is the average rating for the week.
-
In that case, I would swap year for each question. 1 row per question with the rating for that week only.
If the data-sheet is structured with the question number in a column you would use a COUNTIFS function instead of COUNTIF and the criteria would be the date is in the last week and the question is the one on the current row.
If the data is set up so that different questions are on different sheets or in different columns then you can continue with COUNTIF and just reference the appropriate data in your cross sheet references.
-
Ok, that helps. Each question is a column.
