Belajar Excel Dasar : Mencari Predikat dengan menggunakan VLOOKUP

Summary

In this tutorial, we learn how to calculate a student's final score and determine their grade using Excel formulas. The process involves calculating the weighted average of assignments (40%), mid-term (30%), and final exam scores (30%). Afterward, the `PLOOKUP` function is used to match the final score to a predefined grade table. The video explains the step-by-step process, including the use of the arithmetic calculation formula and how to apply the `PLOOKUP` function with precise references, ensuring the grade is accurately assigned based on the final score.

Takeaways

• 😀 Calculate the final grade by applying the weights: 40% for assignments, 30% for the midterm, and 30% for the final exam.
• 😀 Use arithmetic operations (multiplication and addition) in Excel to compute the final grade for each student.
• 😀 The formula for calculating the final grade is: (40% * Tugas) + (30% * UTS) + (30% * UAS).
• 😀 To find the corresponding predicate, use the PLOOKUP function to match the final grade with its predicate from a predefined table.
• 😀 The `PLOOKUP` function searches for the final grade in the lookup table and returns the corresponding predicate.
• 😀 The lookup table ranges from H5 to H14, and the predicates are in the second column of this table.
• 😀 Use `TRUE` in the PLOOKUP function to enable approximate matching, as the final grades may contain decimals.
• 😀 After calculating the final grade for one student, copy the formula down to apply it to other students' rows.
• 😀 Ensure that the lookup table is locked (using F4 in Excel) to prevent it from changing when copying the formula.
• 😀 The predicate results are displayed based on the final grade, such as 'Baik Sekali' for grades above 85.
• 😀 Excel formulas allow you to efficiently calculate grades and automatically generate predicates for large groups of students.

Q & A

What is the purpose of the video?

The purpose of the video is to demonstrate how to calculate a student's final grade based on their assignment, midterm (UTS), and final exam (UAS) scores, and how to determine the corresponding predicate using the VLOOKUP function in Excel.

What are the weightages assigned to each component (Tugas, UTS, and UAS)?

The weightage for each component is as follows: the assignment (Tugas) is 40%, the midterm (UTS) is 30%, and the final exam (UAS) is 30%.

How do you calculate the final grade in the video?

The final grade is calculated by multiplying each component's score by its respective weight and summing them up. For example: 40% of the assignment score, 30% of the midterm score, and 30% of the final exam score.

What Excel formula is used to calculate the final grade?

The formula used to calculate the final grade is: `=40%*E5 + 30%*F5 + 30%*G5`, where E5 is the assignment score, F5 is the midterm score, and G5 is the final exam score.

How do you apply the formula to other rows in the Excel sheet?

Once the formula is entered in one row, you can apply it to the other rows by dragging the formula down using the fill handle in Excel.

What does the VLOOKUP function do in the context of this video?

The VLOOKUP function is used to look up the final grade in a reference table and return the corresponding predicate based on predefined grade ranges.

What is the role of the reference table in this process?

The reference table contains the ranges of numerical grades and their corresponding predicates. The VLOOKUP function uses this table to determine the predicate based on the student's final grade.

Why is the F4 key used in Excel while applying the VLOOKUP function?

The F4 key is used to lock the reference table in the formula, ensuring that it doesn't change as the formula is copied to other cells.

What is the significance of the TRUE parameter in the VLOOKUP function?

The TRUE parameter in the VLOOKUP function is used to find an approximate match, which is appropriate when working with grade ranges that may not have exact matches for each student's score.

How do you copy the VLOOKUP formula for other rows?

After entering the VLOOKUP formula in one cell, you can copy it to the other cells by dragging the formula down using the fill handle in Excel, just like with the final grade formula.