Latihan Praktik Excel Untuk Masuk Kerja #5

Summary

In this Excel tutorial, the instructor walks through a comprehensive car rental pricing exercise using various Excel functions. The video covers the use of VLOOKUP to retrieve rental rates, MID to extract specific characters from codes, and IF to apply conditional logic for discounts. Key points include calculating initial prices, applying discounts for certain car types and rental durations, and finalizing the total cost. Viewers will learn how to efficiently structure formulas to handle complex rental pricing scenarios, with clear instructions on how to implement them step-by-step in Excel.

Takeaways

• 😀 The task involves working with Excel functions to calculate vehicle rental fees and discounts.
• 😀 You will use `VLOOKUP` to look up the rental tariff based on a code that includes vehicle type and model.
• 😀 The first letter of the code determines the vehicle type: 'S' for sedan, 'M' for minibus, and 'B' for pickup truck.
• 😀 The second letter in the code is used to identify the vehicle type, such as 'A', 'B', or 'C'.
• 😀 `MID` and `LEFT` functions help extract specific characters from the rental code to determine the vehicle's type and model.
• 😀 The `VLOOKUP` function is used to search for the corresponding tariff in the tariff table using the extracted code values.
• 😀 Conditional logic with the `IF` function is used to check if a discount should be applied based on the vehicle type and rental duration.
• 😀 Discounts are applied only for sedans or minibuses rented for 3 days or more.
• 😀 The `HLOOKUP` function is used to retrieve the appropriate discount percentage from the discount table based on the vehicle type.
• 😀 The final payment is calculated by multiplying the base price (tariff) by the number of days rented, and then subtracting the discount if applicable.
• 😀 The final output also includes automatic calculations for total amounts using Excel's summing functions, which can be quickly applied using `ALT` + `=`.

Q & A

What is the purpose of using the MID function in the Excel formula?

The MID function is used to extract the second character from the rental code to determine the vehicle type, such as 'A', 'B', or 'C' which is referenced in the tariff table.

How does the VLOOKUP function work in this script?

VLOOKUP is used to search for the value from the MID function (the second character of the rental code) in the tariff table. After finding the type, it retrieves the corresponding rate from a specified column.

Why is the LEFT function used in this Excel formula?

The LEFT function is used to extract the first character of the rental code to identify the vehicle type, such as 'S' for Sedan, 'M' for Minibus, or 'B' for Pickup Truck.

What is the role of the IF function in the formula for determining which column to look up in the tariff table?

The IF function is used to check the first character of the rental code. If it's 'S', 'M', or 'B', it determines which column in the tariff table to use, ensuring the correct price is selected based on the vehicle type.

How is the discount determined in the Excel formula?

The discount is determined by checking if the rental is for a Sedan or Minibus and if the rental duration is 3 days or more. If both conditions are true, the discount is applied based on the values in the discount table.

What is the purpose of the HLOOKUP function in the discount calculation?

HLOOKUP is used to search for the first character of the rental code ('S' for Sedan or 'M' for Minibus) in the discount table and retrieve the corresponding discount percentage from the second row.

Why is the formula for calculating the total amount so complex?

The formula is complex because it includes multiple conditions to determine the vehicle type, rental duration, applicable discount, and final price, all of which need to be calculated based on specific criteria.

What happens if the discount conditions are not met?

If the discount conditions are not met (e.g., the vehicle is not a Sedan or Minibus, or the rental duration is less than 3 days), the discount is not applied, and the final price remains the same.

What does the formula do if the rental code is 'SA'?

If the rental code is 'SA', the first character 'S' indicates a Sedan, and the second character 'A' refers to a specific type in the tariff table. The formula then calculates the rental fee accordingly.

How does the formula calculate the initial rental price?

The initial rental price is calculated by multiplying the rental rate (from the tariff table) by the rental duration (the number of days). This is done using a simple multiplication formula in Excel.