Cara Menghitung Nilai Ijazah Sesuai Juknis

Summary

This video demonstrates how to calculate and create SMP graduation scores based on official guidelines. It explains how to compute 60% from the average of student report cards from semesters 1 to 5, and 40% from the semester 6 exam results. The process includes using formulas in Excel to calculate these values, as well as how to compile and format the results in a clear and organized way. The video also offers a step-by-step guide to setting up and automating the process for ease of use and presentation.

Takeaways

• 😀 The video explains how to calculate student grades based on specific guidelines, with a focus on 60% from average report card grades (semesters 1-5) and 40% from the exam results (semester 6).
• 😀 It uses Excel to automate the calculation process for both the report card and the final grade (ijazah).
• 😀 The process starts with entering and summing up the grades from multiple semesters, then applying the weightings for the final calculation.
• 😀 The 'AVERAGE' function in Excel is used to calculate the average grades for semesters 1 through 5, followed by applying 60% of this average to the final score.
• 😀 The 'IF' function is used to handle empty cells and copy previous values when necessary, ensuring the calculations remain consistent.
• 😀 The 40% weight is assigned to the exam grades (semester 6), which are entered and calculated similarly to the report card grades.
• 😀 The final ijazah score is calculated by adding the weighted averages from both the report card and the exam.
• 😀 The video demonstrates how to set up a 'helper table' to manage and organize the data more effectively, particularly when dealing with merged cells and different layouts.
• 😀 Excel formulas like 'INDEX' and 'MATCH' are used for lookup functions, allowing the system to pull data based on two criteria: student name and subject.
• 😀 The tutorial emphasizes formatting and cleaning up the Excel sheet for clarity, including hiding or adjusting fonts to make the data visually consistent.
• 😀 The final step involves using formulas to generate a clean report for each student, ensuring that the final ijazah grade is easily accessible and well-organized.

Q & A

What is the purpose of the video?

The video explains how to calculate the final grades (ijazah) for students based on specific guidelines, using Excel formulas to combine both semester report card scores and exam results.

What are the two key components used to calculate the final grade?

The two components are: 60% of the average report card scores from semesters 1 to 5, and 40% from the semester 6 exam score.

What Excel function is used to sum the values in a range?

The 'SUM' function is used to add up the values in a specified range.

How is the average report card score for semesters 1 to 5 calculated?

The average score is calculated using the 'AVERAGE' function, which takes the values from semesters 1 to 5 and computes the mean.

How is 60% of the report card score calculated in the video?

To calculate 60% of the report card score, the average score for semesters 1 to 5 is multiplied by 60%.

How is the 40% exam score from semester 6 incorporated into the final grade?

The 40% exam score is calculated by multiplying the semester 6 exam score by 40%. This is then added to the 60% portion of the report card score to determine the final grade.

What function is used to calculate the final grade or ijazah score?

The final grade is calculated by adding the 60% weighted report card score and the 40% weighted exam score together.

How are the student names and subject names used in the data reorganization?

The student names and subject names are used in a 'transpose' function to rearrange the data, ensuring it is in the correct format for further processing.

What is the role of the 'IF' function in the video?

The 'IF' function is used to check whether a cell is empty and, if so, to reference the value from the cell above it. This helps handle missing data or inconsistencies in the table.

How does the 'INDEX' and 'MATCH' combination function work in the video?

The 'INDEX' function retrieves the final grade from a table, while the 'MATCH' function is used to locate the student’s name and subject, allowing the script to correctly match and pull the relevant final grade for each student.