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?
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.
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: