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?
3
-
Just create a second sheet calculating the sum for the ranges using COUNTIF, and create the chart based on that second sheet. If you encounter specific problem, just add them to your question.– tohuwawohuFeb 17, 2014 at 10:15
-
Ok with COUNTIF I can now count according to set ranges. I don't know how to include multiple conditions (smaller than the next and larger than the previous range value), experimenting with COUNTIFS failed. Furthermore, is there a way of automatically editing the formulas for the counting column depending on the row? I would need to implement in row n: "count all instances of column A which have value at most B_n and larger than B_{n-1}".– Gandalf LechnerFeb 17, 2014 at 12:04
-
Sorry, COUNTIF wasn't a good hint - SUMPRODUCT will do the trick. See my answer.– tohuwawohuFeb 17, 2014 at 15:28
Add a comment
|
2 Answers
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:
=CONCATENATE(A20;" to ";B20)
The SUMPRODUCT formula looks as follows:
=SUMPRODUCT($A$2:$A$16 >= A20; $A$2:$A$16 <= B20; $B$2:$B$16)
(both formulas for the first range 1 to 5)
The resulting helper table looks as follows:
Now, you can create a chart easily based on C19:D22:
Since the ranges are set by the cell values A20:B22, you can easily modify the result set and the chart. It should be no problem to adapt the example to other raw data.
answered Feb 17, 2014 at 15:27
-
That's good already, thanks! Is there maybe a way of automating the SUMPRODUCT formula in case I have many ranges and don't want to type it so often? Feb 17, 2014 at 16:26
-
1Yes - since the cell references for the range "border values" (A20, B20) are relative, you can drag the formula down from D20 to D22 or further. Copying the formula to other cells shoul work, too. Feb 17, 2014 at 16:50
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.
