Creating Graphs and Charts

From the previous tutorial we have learned how to LOOK UP a value. We have successfully converted the student's raw scores to its equivalent rating... Now, we will learn how to create a Chart and a Graph base from the student's final grades.

Before we proceed, let us first summarize the ratings by creating a frequency distribution table.

1. Make a list of all it categories on any cell of the same worksheet and make the use of the countif formula with format =countif(range;crieteria)

2. To get the frequency of COUNT, type countif(highlight the data under GRADES for the range--- put a colon (;)--- highlight the cell with 5 category--- type ) ---press enter

3. Same step will follow to get the frequency of the following grades.

From the frequency distribution table we have made,

1. To get the percentile of the count, type =highlight the cell that contains the frequency of count ---type / (indicate division sign) ---click the total number of students (ex. 50) ---press enter. You can multiply it by 100 or simply click the percentile icon (%) on the toolbar.

2. The same procedure would follow to get the percentile of the following grades.

CREATING A CHART

In general, to create a chart:

1. Select the cells that contain the data you want to use in the chart.

2. Click the INSERT tab on the toolbar.

3. Click the type of chart you want to create.

MODIFY A CHART

Once you have created a chart you can do several things to modify the chart.

Chart Wizard
The Chart Wizard appear as a dialog box when you click on the chart. The steps are located at the left side: Chart Type, Data Range, Data Series, Chart Elements

Within the Data Range and Data Series you can control the labels, and analysis.

Within the Chart Elements you can put a title or if you want a subtitle on your chart and axes.

PIE CHART
-provides another graphical device for presenting relative frequency and percent frequency distributions for categorical data.

To make a pie chart,

1. Highlight the categories of variable grade and count from the frequency distribution table we have made.

2. Click on the INSERT tab ---click on Chart ---click on Pie chart. Chart appears.

The output should be

Note: To edit the chart, right click on the chart and task bar of OpenOffice will appear like this.

Format Data Series: changing the color, direction and styles of the chart.

Format Data Labels: changing fonts, formats etc.

Chart Type: changing the appearance of the chart

3. You can put title or subtitle from the chart elements on the chart wizard, as discussed above.

LINE GRAPH

To create a Line Graph, we just have to follow the steps we made in creating a Pie Graph.

1. Highlight the categories of variable grade and count from the frequency distribution table we have made.

2.  Click on the INSERT tab ---click on Chart ---click on Line Graph.

3. Click on NEXT to modify the graph. The output should be:

Note: To edit the graph, right click on the graph and task bar of OpenOffice will appear.

VLOOKUP Function Tutorial

Another chapter of my Master’s degree journey has opened, the second semester. One of my subjects is Basic Computer Application where I need to learn “more” about basic applications in computer.

One of the tutorial requests is how do you look up a value on one worksheet and use it on another worksheet.  So what we will be going to do is to convert the student’s raw score to its equivalent College Rating.

• As we can see, we have here the student’s raw scores from the three examinations, computing the average we have the formula =AVERAGE(C5;D5;E5), just click on the correspondent cells.

• We have now computed the average; the next question is how do we convert it to its equivalent college rating? For example a grade of 80-85 is equivalent to 1.75.

When working with student grades, of course we don't want to create a lookup table where we have to enter every possible average. Instead, our lookup table should have the starting average for each grade. Based on the table below, all averages of 96 or over should receive a grade of 1.00.

THE LOOKUP TABLE

In this example, the lookup table is created on another sheet. To create the lookup table:

1. In the first column, enter the minimum average for each grade.
2. In the second column, enter the matching Grade for each average.
3. Sort the averages in ascending order. 

THE REPORT OF GRADES

The scores are entered on another sheet (in my example, I named the lookup table as GRADES and the other one as GRADES REPORT), where a VLOOKUP formula calculates the final grade.

CALCULATING THE FINAL GRADE USING THE VLOOKUP FORMULA

Here are some steps on how to use the VLOOKUP formula in calculating the final grades of the students.

Step 1: Click INSERT and go to FUNCTION.

This box will appear. On the formula box, type the formula for VLOOKUP which is =VLOOKUP(

STEP 2: The syntax for the VLOOKUP function is: =VLOOKUP(searchcriterion;array;index;sortorder)

wherein: 

search criterion	-	The value that you want to search for
array	-	The array of data, that is to be searched for the lookup value. The Vlookup function searches in the left-most column of the array
index	-	The column number of the supplied array, that you want to return a value from
sort order	-	An optional logical argument, which can be set to TRUE or FALSE, meaning : TRUE - Find the closest match below the search criterion if the exact value is not found (Note: with this option, the left-hand column of the array must be in ascending order) FALSE - Find an exact match to the search criterion - if an exact is not found, the function returns an error If the [range_lookup] value is omitted, it takes the default value of TRUE

STEP 3: Given the above information, we can now provide the corresponding values. The formula should be =VLOOKUP(F5;Grades.$A$2:$B$8;2;TRUE, then click OK.

HOW IT WORKS?

1. In the VLOOKUP formula, the first argument, F5, is the value that we want to look up in the Grades table.
2. The second argument, Grades.$A$2:$B$8, is the location of the lookup table. The reference is absolute $A$2:$B$8, instead of relative, so that the VLOOKUP formula can be copied to other cells, and the table reference won't change.
3. The third argument, 2, is the column number in the lookup table, where the Grades are located. When an average match is found in the first column, the equivalent grade from the 2nd column will be returned.
4. The fourth argument, TRUE, means that an approximate match is okay. This is the default setting, and if you omit TRUE, you'll get the same result from the formula.

STEP 4: After computing the final grade or the equivalent grade of the average, drag the formula down to the remaining cells in the grades report.