Vlookup Function
The vlookup stands for Vertical lookup and it allows you to determine the information of a specific data from a set of data or database. It returns a value from any cell of a range of cells.
The following steps will teach you how to calculate the
grades of your students using the “vlookup” function as well as some
additional ways of assessing the grades of your students. We will be using this data as our guide.
Step 1. Compute the Average
- As you open your OpenOffice Calc, on Sheet1, enter the Name of your students, their grades for three terms (e.g. T1G for the first term), Average and the last column for the Final Grade. To get the Average:
- Click cell E2, type =AVERAGE(B2:D2), then press ENTER.
- To minimize the time for calculating the average for each student, activate the Cross by double clicking the small box at the bottom-right of the cell or drag it down to the last cell.
- There are many ways of computing the average, one is the command that we used above if you know the correct function. Another is by clicking the Insert, then Function(Ctrl +F2). The Function Wizard will appear showing the different functions. From the Category drop down list click Statistical, then double click Average from the Function list. The Function Wizard dialog box will change like the image below, click “select” to select the range of cells to be calculated or simply write them (e.g. B3:D3). Then click “shrink” then click OK.
- For faster references, you may keep the Function pane by clicking again Insert, then Function list. A function pane will appear on the right side of the screen showing the recent functions that you used. To remove it, click “hide”.
Step
2. Make a Legend
- To get the Final Grade, first, we need to create the grading system as legend. The grading system may consist of number system (1.00, 1.25, 1.50, 2.00 … 5.00) or letters (A+, A-, … F). In Sheet2, enter them in ascending order with the equivalent grade on the first column and the Final Grade on the second column.
o
e.g. Passing grade of 60% : Having a grade point average of 60.00 is equivalent to 3.00 while all 90.00 and above is
equivalent to 1.00.
Step 3. Create the Vlookup Table
- Now that we have our legend, go back to Sheet1 and click the first cell below the Final Grade (F2). We will now use the vlookup function to calculate the Final Grade.
o
Type =VLOOKUP(E2;Sheet2.A$2:B$11;1;1) then
press Enter. Double click again the
small box or drag down to copy the formula to the other students.
§
Search criterion
is the grade point average of your
student
§
Array is
your grading system. You may select the grading system (all the equivalent
grades and final grades) directly from Sheet2. Inserting “$” between the column letter and row number e.g. Sheet2.A$2;B$11 will make your cell or range of cells permanent so that the
range will not change when you try to drag and copy the formula for the rest of
the students.
§
Index is
either 1 (column 1) or 2 (column 2). In here, it should be 2
because column 2 is our Final Grade.
§
Sort order is
either 1 (approximate) or 2 (exact). In here, it should be 1
because approximately all the grades higher than the equivalent grade are
considered.
Step 4. Creating the Summary Table
- Step 1 to Step 3 allows you to calculate the grades of your students easily using the vlookup function. The following steps are additional information about assessing the grades of the students. This includes a Summary table, setting and changing the Passing Grade as well as creating a Graph.
o
In
creating a Summary table, it should
be one column to the tight and one row below to avoid changes in your summary
table when you make necessary Insertions or Deletions in your master list
table.
o
First
we enter the Final Grade of the
grading system, and then we will get the Frequency
of each grade by counting the number of students. Finally is to present them in
Percentage.
o
To get
the frequency or Count, click the
first cell below the Count (I29) then type =COUNTIF(F$2:F$26;H29), then Enter and double click or drag down the Cross.
§
Range is the
range of your grades, specifically all of the final grades. Do not forget to
put the “$” sign.
§
Criterion is
the Grade to be counted.
o
To get
the total count, click cell I39 then type =sum(I29:I38), press Enter. From here, we can tell that the range of cells to be counted
is from cell I29 to cell I38.
o
To get
the Percentage, click cell range
J29:J39, then click the Percentage
button to format the cells.
 |
| Percentage |
o
In cell
J29, type =I29/I$39, then press Enter and
double click or drag down the box.
o To
remove the decimal places, select again the cell range J29:J39 then click the Delete
Decimal Place button twice. To bring back the decimal places, click Add
Decimal Place button.
 |
| Remove Decimal Place |
 |
| Add Decimal Place |
Step 5. Setting and Changing Passing Grade
- Setting and changing the passing grade makes you asses even more the grades of your students to determine the number of students who will pass and fail when you change the passing grade.
o
In Sheet2, type “Passing” in cell A14 and “Increment”
in cell A15.
o
In cell
B14, change the passing grade from 60 percent lower it down to 50 percent.
o In cell
B15, type =(100-B14)/10 then press Enter.
“100” less the passing grade, all divided by the total number of grades.
o
To
change the “equivalent grade” click cell A4 then type =B14 then press Enter so
that it is equivalent to a Final Grade of 3.00. Then on cell A5, type =A4+B$15, press Enter then double click or drag down the Cross.
o Look
at your master list table as well as your summary
table to see the changes.
Step 6. Creating a Graph
- A graphical representation is also one of the ways of presenting your grades.
o We will
use the summary table in creating a Pie
Chart.
o Click Insert, then select Chart from the drop down list or simply
click the button from the formatting toolbar. Select Pie from the Choose a Chart Type, then click Normal from the chart styles.
 |
| Chart |
o
Click Next or 2. Data Range from the Steps.
Select the data range by clicking “select” then select the cell range H28:I38 or type $Sheet1.$H$28:$I$38 without clicking the “select”. Mark the Data series in columns and put a
check on First row as label
since our table is represented that way. Click Next.
o
On the Data Series, select GRADE, then on Data Ranges click Name. “Select” the cell of the title Grade H28 or simply type $Sheet1.$H$28. Click Y-values
then “select” the cell range containing the grades H29:H38 or simply type $Sheet1.$H$29:$H$38.
o
Follow
the same steps for COUNT but instead
use the cell and cell ranges under the Count. Then on the Categories, “select” the cell range H29:H38 or simply type $Sheet1.$H$29:$H$38
to make your Grades column as your Legend.
o
On Step
4. Chart Elements, type the title of
your chart or graph “Grade Distribution”
then put a check on Display
Legend and mark Right to show
the legend on the right.
o
To show
the number of counts, right click on
the chart then select Insert data labels.
If the option would not appear, deselect the chart then double click the chart
again.
o
To
change the data label from Count to
Percentage, right click on the Data Label then select Data Ranges. On the Data Series tab, select COUNT then
click Remove and the click Add. Select the Unnamed Series and customize it by following the steps on how you
did on GRADE and COUNT. This time “select” name as % (J28) or simply type $Sheet1.$J$28.
Then on Y-values “select” the cell
range J29:J38 or simply type $Sheet1.$J$29:$J$38 then click OK.
o
You can
also change the pie chart into a different graph by clicking the Chart
type button.
 |
| Chart type |
You have just created your first grade in your teaching
career! There are many options in calculating your grades and transforming them
into different graphical representations. Feel free to experiment with your OpenOffice Calc to see its features but
make sure that you will use only those which are appropriate to your work.
Walang komento:
Mag-post ng isang Komento