Create chart with total for each score

by spiffy   Last Updated July 19, 2018 18:03 PM

I have a column within a Google Spreadsheet with values like this:

96
97
96
100
97
97
100
97
95
95
96

I'd like to add a bar chart where each bar represents one of the values and the height of the bar represents the number of occurrences of that value. For example, 97 would be 4, 96 would be 3 and so on. What are the steps I'd need to follow to do that?



Answers 2


Let's assume your data are in cells A1:A11 (as above).

First, use a combination of the SORT and UNIQUE functions to create a list of each unique value in your data. For example, you could put the formula =SORT(UNIQUE(A1:A7)) in cell B1 to create the list. The resulting list will have four values (95, 96, 97, 100) in, say, cells B1:B4.

Then, use the FREQUENCY function to get a count of the number of occurrences of each value. The FREQUENCY function takes two range arguments: the data range and the class range. The class range is the values for which you want the frequencies calculated, which in this case would be in cells B1:B4. Assume you've entered the FREQUENCY function in cell C1.

Now create a chart using the range B1:C4 as input. When you specify the chart, be sure to check "Use column B as labels". The setup and a possible chart are shown below.

example spreadsheet layout

enter image description here

chuff
chuff
February 08, 2014 23:57 PM

No formulae are needed. Just select the range, Insert, Chart..., select Histogram, Insert. For the sample as above a bin size of 2 will be automatically selected but this can be changed to 1 by right-clicking the chart area, Advanced edit..., entering 1 for Bucket size and Update.

Result should look something like:

WA56777 example

pnuts
pnuts
February 18, 2014 16:55 PM

Related Questions