TUTORIAL 3: Tutorial on how to make bar chart and pie chart in OpenOffice Calc

In this tutorial, I am going to show you how to make bar chart and pie chart using an OpenOffice Calc. The version of OpenOffice used in this tutorial is Apache OpenOffice 4.1.1. 

Here is an example of a data (from my Tutorial 2) where Grades, Count and Percentage of the grade distribution of students in Chemistry are shown.
Note: All of the students names and scores which appeared herewith are only  hypothetical and do not reflect their real performance in class. This is for description and visual purposes only.  

To create a bar chart:
Step 1: Highlight the cells where the data is located. This data will be used for the bar chart.

Step 2: Click the chart symbol on top.

Step 3: Another tab will appear (Chart Wizard) which asks you to choose a chart type. Since we are creating a bar chart (with vertical bars), choose column, then click next.

Step 4: Next, you are asked to choose the data range. But in here, we will focus more on Data Series, which is in Number 3.

Step 5: Click on the Data Series. You are to customize the data ranges for individual data series.

Step 6: You now have Data Series, namely Grade and Count. Since we are to create a bar chart based on how many of the students got a particular grade, the data series 'grade' will be omitted. Click grade, then click Remove.

Step 7: We now have the Count on our Data Series. To designate for categories, click the Categories portion, and highlight cells under Count. 

Step 8: Then click the Y-Values. To designate for the Categories, highlight the cells under Grade. Then, click Next.

Step 9:  Next, will be the Chart Elements. Enter your desired Title, Subtitles, Legend for the x and y axes. Click Finish.

Step 10:  A bar chart will appear showing the grade distribution and number of student (count) of a particular grade. 

In your bar chart, you can create your pie chart with it.

To transform a bar chart into a pie chart:
Step 1: Click on the center of the vertical bars until small green squares will appear.

Step 2: Right click, then click on Chart Type.

Step 3: Choose on Pie, then click OK.

 Step 4: A pie chart showing the grade distributions of students in different colors will appear.

 
Congratulations! You now have a pie chart showing the grade distribution of your students in Chemistry. 

What if you want to have a pie chart showing the percentage of grades? You can just edit the previous pie chart which you have created.

Step 1: Click on the center of the pie until small, green squares will appear. 

  Step 2: Right-click. Click on Data Ranges.

   Step 3: Another tab will appear (Data Ranges). It shows the Data Series, which is Count, and the Data Ranges. To designate Categories, highlight cells under Count. 

Step 4: To designate Categories of the Y-values, highlight cells under Grade.

 
Step 5: By clicking OK, you'll have the grade distribution of your students with their corresponding percentages. 

You can also change the colors of the pie if you want a better hue of colors. 
Step 6: Double-click on the center of the pie, and another tab will appear. Choose colors you desire. Then click OK. 

You now have a colorful Pie chart. ^_^

Thank you! 

Please read my other tutorials! :)

All Rights Reserved (2014)
Intellectual Property of:
Josephine Joy V. Tolentino
Master of Science in Biology student
College of Arts and Sciences
Department of Biological Sciences
Central Luzon State University
Science City of Munoz, Nueva Ecija
   Philippines

TUTORIAL 2: Tutorial on how to get AVERAGE and to use vlookup in OpenOffice Calc

Let’s say, you are a Chemistry teacher, and in your class, you had four (4) Term Examinations. Your student, Eldridge, got the following scores: 64/100, 62/100, 58,100 and 59/100. If the passing score is 60, which is equivalent to 3.00; what will be your student’s average and his corresponding final grade? Will he pass the subject?

Well, in this tutorial, I am going to show you how to compute the average and to indicate the equivalent or corresponding grades of your students using vlook-up in OpenOffice Calc. The version of OpenOffice used in this tutorial is Apache OpenOffice 4.1.1.

Below is an example of a data sheet containing student’s names and their corresponding grades in a particular examination. 
Note: All of the students names and scores which appeared herewith are only  hypothetical and do not reflect their real performance in class. This is for description and visual purposes only.  

   To compute for the AVERAGE:

Step 1: Click on the cell to parallel to the student’s names and term exams grades, enter equal ( = ) sign; type AVERAGE()

 Step 2: Drag the cells that you are determining the average; that is, the student's term examinations grades. 

Step 3: Press ENTER. You now have the student's AVERAGE in all his four term examinations! 

Now, in order for you to get all your student's average, you don't need to do the same procedures repeatedly! Instead, you can just drag the same formula until the end of your data sheet. 

Step 4: When you point the pointer at lower right corner of the cell where you had just computed the average, a plus (+) sign will appear. Click it and drag it (by holding it without releasing) until you reach the last cell of your data sheet, and then release. 

You now have the average scores of the students in your class! Your next target is, to determine the FINAL GRADE of your students. 

It is unnecessary for you to type individually the final grade of your students. We now have the role of vlook-up to answer this! 

But before you can compute for the final grade, you need to have a look-up table. It is where the average scores will have its equivalent grades. In order to do this, you must have another data sheet (Sheet 2) that is separate from your original data sheet (Sheet 1).

Step 1: Make and edit your table accordingly. Be sure you are doing this on another data sheet! 

Since we already have our look-up table, where each average scores has its equivalent grade, the final grade can be now computed. 

To compute for the FINAL GRADE:

Step 1: Click on the cell to parallel to the student’s average, enter equal ( = ) sign; type VLOOKUP(

Step 2: Click on the Function sign(fx) on top left of the table. Another tab will show up (Function Wizard).

Step 3: The Search_criterion is the Average. For the array, it is the Look-up table (Sheet 2). For the Index, just type number 2. Lastly, for the sort_order, type TRUE. 
     Step 3.1: Search criterion: AVERAGE

     Step 3.2: Array: Look-up table on Sheet 2

   Step 3.3: Index: type 2; sort_order: type TRUE.

Step 4: Click on OK. You now have the final grade of that student, Eldrige, which is 3. This means he PASSED Chemistry!

Now, in order for you to get all your student's final grades, you don't need to do the same procedures repeatedly!  Instead, you can just drag the same formula until the end of your data sheet. 

Step 5: When you point the pointer at lower right corner of the cell where you had just computed the average, a plus (+) sign will appear. Click it and drag it (by holding it without releasing) until you reach the last cell of your data sheet, and then release. 

Congratulations! You now have the average and the final grades of the students! 

Want to know how many of your students got 3.00? ? Or how many got 1.00 and so on? 

We can compute this by using Count-If. 

It is important that we place this statistics of grades on separate sheet, aside from your original sheet (Sheet 1). 

To compute for the statistics of the grades:

Step 1: Click on the cell parallel to the grade. Type the equal sign ( = ), and type COUNTIF(

Step 2: Click the Function sign (fx) on top left of the table. Another tab (Function Wizard) will appear, which asks you the Range and Criteria. 

Step 3: The Range is the Final Grade on Sheet 1, and the Criteria is the Grade on Sheet 2. For the range, just drag the whole Final grade up to the last cell of the data, and do the same for the grade. 

      Step 3.1: Select Range

Step 3.2: Select Criteria

Step 3.3: Another tab will appear showing the range and criteria you have selected. Click OK.

Step 4: By clicking OK, you will now have the number (count) of students having a grade of 3. Just drag down to get the counts for the remaining grades. 

Step 5: Make sure you got the same total number of students. To check, enter another cell just below your last count. Type equal sign ( = ), type SUM(, then drag the whole cells under count. 

If you want to know the corresponding percentage of a particular grade, then: 

  Step 6: Type equal sign ( = ), then click the cell under count, the division sign ( / ), then click the total count. To have the absolute value, just type a dollar sign($) in between the name of the total count. Hit Enter. 

Step 7: Press Enter.

Step 8: Just drag down until you reach the last cell of your data to get the percentage of the whole data. Make sure you'll get 100%. 

If you want to know the highest and lowest grades of your students:

Step 9: In getting the highest average, type equal sign ( = ), then type MAX(

Step 10: Highlight the whole Average in Sheet 1. 

Step 11: By pressing ENTER, you'll be getting the highest grade of your students.

To get the lowest grade of your students:

Step 12: Type equal sign ( = ), type MIN(

Step 13: Highlight the whole Average on Sheet 1.

Step 14: By pressing ENTER,  you'll be getting the lowest grade of your students.

Good thing, all of your students passed your subject, Chemistry! :)

Thank you! 

Please read my other tutorials! :)

All Rights Reserved (2014)
Intellectual Property of:
Josephine Joy V. Tolentino
Master of Science in Biology student
College of Arts and Sciences
Department of Biological Sciences
Central Luzon State University
Science City of Munoz, Nueva Ecija
   Philippines