I would like to create charts from data in a libreoffice-calc document, displaying the results of an exam. For example, one column would contain numbers from 0 to, say, 100 (points reached), and I would like to specify ranges, like 0-10, 10-20, or similar, and have a bar chart displaying how many entries in that column lie in the given ranges. Automatically calculating a mean value and displaying it in the chart would be a bonus, but not strictly necessary. Any ideas how to achieve that?
Gererally, i recommend to create a helper table that does the calculation, and to create the chart on the basis of that helper table. To calculate the subtotals, you could use SUMPRODUCT. SUMIF would be possible, too, but you would have to create the formula for each subtotal manually.
I will demonstrate the solution with a simplified example: Points are from 1 to 15, with three groups of subtotals (1-5; 6-10: 11-15):
The helper table looks as follows:
The Min/Max values will be used for the range labels as well as for the count. The range labels are created using a simple concatenation:
The SUMPRODUCT formula looks as follows:
(both formulas for the first range
The resulting helper table looks as follows:
Now, you can create a chart easily based on
Since the ranges are set by the cell values
I would simply use the FREQUENCY function for the first part of your question.
You set up a column (if your data are in columns) with the lower borders of your exam groups, for instance 0 10 20 49 100
and in the column to its right you enter the FREQUENCY function in the first line with the help of the functions wizard.
For charting you open the chart wizard , select column chart and use the first column as labels and the second as the data column.
That should do the trick.