Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  tohuwawohu Feb 17 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 Lechner Feb 17 at 12:04
    
Sorry, COUNTIF wasn't a good hint - SUMPRODUCT will do the trick. See my answer. –  tohuwawohu Feb 17 at 15:28

1 Answer 1

up vote 1 down vote accepted

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):

SumProduct Sample Data

The helper table looks as follows:

Sumproduct helper table

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:

Sumproduct helper table 2

Now, you can create a chart easily based on C19:D22:

Sumproduct Chart

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.

share|improve this answer
    
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? –  Gandalf Lechner Feb 17 at 16:26
1  
Yes - 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. –  tohuwawohu Feb 17 at 16:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.