Question

Step Instruction Earned Possible On the RentalData worksheet, in cell E6, enter a date formula to determine the length of rental in days. Copy this formula to the range E7 E32. 2 In cell G6, use the appropriate lookup and reference function to retrieve the rental rate from the named range RentalRates. The function should look for an exact matching value from column A in the data. Copy the function down the column to cell G32. 4 In cell H6, enter a formula to determine any discount that should be applied. If the payment method was Express Miles or Rewards, the customer should receive the discount shown in B42. If no discount should be applied, the formula should return a zero. Use the named range for cell B42, not the cell address in this formula. Copy the formula to the range H7 H32. 6 2.25 In cell K6, enter a formula to determine the total cost of the rental based on the daily rate, the number of days rented, the cost of gas, and any discount given to the customer. Note that the discount applies only to the car rental, not the cost of gas Copy the formula to the range K7:K32 10

tRYING TO FIND THESE FORMULAS!

THASNK YOU

Answer 1

Step 2: Calculate no. of days

This is pretty straightforward. Excel lets you use arithmetic operations on dates. So, if you want to calculate no. of days between 2 dates, you simply subtract the two. The formula is as shown:

Step 4: Lookup function

For this you use a function called VLOOKUP. The formula implementation looks like this:

How it works?

The function takes in four values which are:

• lookup_value is the value that you want to look up. You want to look up the auto type so, you will reference the "Auto type" column here.
• table_array is the array that contains the table where you want to look for the above value. So, here you will reference the rental rates table.
• col_index_num is the index of column that contains the value you want to return (the daily rate in this case). How this works is that the first column in the table that you selected in the second step, has the index of 1. So, the daily rate column will have an index of 2.
• range lookup asks if you want an exact match or approximate match. In this case you want an exact match, so you are going to enter 0 here.

Step 6: Discount formula

You can use the IF statement along with OR function here. It looks like this

IF function takes in three values IF(logical statement, value if true, value if false)

• First is the logical statement. In the given case, you want to check if payment method is either "Express Miles" or "Rewards". In order to check for both, we can use the OR function which returns "true" if either of the two or more statements inside it are true.
• Second value is what you want to be displayed if the logical statement returns true. In this case it is the discount.
• This is the value that should be displayed if logical statement is false. 0 in this case.

Step 8: Total cost

Days rented * Daily rate * (1 - discount) + Gas cost