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