in cell h6, enter a formula to determine any discount that should be applied. if
ID: 3805185 • Letter: I
Question
in cell h6, enter a formula to determine any discount that should be applied. if the payment method was express miles or reward, the customer should receive the discount shown in b24. if no discound should be applied, the formula should return a zero.
i could not understand how to put the formula????
Also, this part in cell j6, enter the correct formula to determine the cost of gas if the customer chose that option as indicated in colum i.
Explanation / Answer
To write a formula first select the cell where you want to add a formula. In the above spreadsheet, we have to write a formula to calculate the disacount. Select the cell H6 and write the following as is
=IF(OR(EXACT(LOWER(F6), "credit card"), EXACT(LOWER(F6), "express miles")),PRODUCT(MID(G6,2,LEN(G6)), B42),0)
The above formula states that if value in column F, i.e payment methoed, is either "Credit card" or "Express miles", then a discount(which is mentioned in cell B42) is applicable.
The functions used are described below
LOWER - Converts the value to lowercase
EXACT - Matches if the two value parameters have the same value
OR - Performs an OR operation on the values passed as the parameter
IF - Performs a logical operation
PRODUCT - Performs multiplication of the numbers passed
MID - Extracts a sub string from a String
LEN - Returns the length of the parameter
Once you have written the formula for column H6, press enter.
To copy the formula for the rest of the column, select cell H6, take the cursor at the bottom right corner of the cell and double click.
The formula for finding the cost of gas is
=IF(EXACT(LOWER(I6), "y"),B44,0)
The B44 will remain same for all the cells.
The formula for cell K6, i.e. Total cost is
=SUM(MID(G7,2,LEN(G7)),H7,I7)
OUTPUT:
Express Car Rental Rentals Initiated in Week Starting 4/5/2015 Created by Jason Easton Auto Type Auto Id Date Rented Date Returned/or Expected Return #Days Rented Payment Method Daily Rate Amount of Discount Gas Option Cost of Gas Total Cost Green Collection 988 04-05-2015 04-08-2015 3 Credit Card $49.99 9.998 n 0 59.988 Compact/Midsize 275 04-05-2015 04-10-2015 5 COD $76.49 0 n 0 76.49 Compact/Midsize 277 04-09-2015 04-10-2015 1 Express Miles $76.49 76.49 n 0 152.98 Green Collection 990 04-05-2015 04-06-2015 1 Credit Card $49.99 49.99 n 0 99.98 Fullsize/Standard 350 04-05-2015 04-08-2015 3 Credit Card $84.99 84.99 y $65.00 169.98 SUV/Minivan 550 04-05-2015 04-09-2015 4 Rewards $104.99 0 y $65.00 104.99 SUV/Minivan 551 04-07-2015 04-10-2015 3 Credit Card $104.99 104.99 n 0 209.98 Fullsize/Standard 352 04-07-2015 04-09-2015 2 Credit Card $84.99 84.99 y $65.00 169.98 Green Collection 989 04-06-2015 04-09-2015 3 Credit Card $49.99 49.99 n 0 99.98 Compact/Midsize 275 04-11-2015 4/18/2015 7 COD $76.49 0 n 0 76.49 Fullsize/Standard 355 04-09-2015 4/16/2015 7 Credit Card $84.99 84.99 y $65.00 169.98 Fullsize/Standard 356 04-06-2015 04-08-2015 2 Credit Card $84.99 84.99 y $65.00 169.98 Fullsize/Standard 350 04-09-2015 4/13/2015 4 Rewards $84.99 0 y $65.00 84.99 SUV/Minivan 553 04-08-2015 4/14/2015 6 Express Miles $104.99 104.99 y $65.00 209.98 SUV/Minivan 550 04-11-2015 4/17/2015 6 COD $104.99 0 y $65.00 104.99 Green Collection 988 04-09-2015 4/15/2015 6 Credit Card $49.99 49.99 n 0 99.98 Green Collection 989 04-10-2015 4/16/2015 6 Express Miles $49.99 49.99 n 0 99.98 Green Collection 990 04-07-2015 04-10-2015 3 Credit Card $49.99 49.99 n 0 99.98 Fullsize/Standard 356 04-09-2015 04-10-2015 1 Rewards $84.99 0 n 0 84.99 SUV/Minivan 551 04-11-2015 4/15/2015 4 Express Miles $104.99 104.99 y $65.00 209.98 SUV/Minivan 554 04-07-2015 04-09-2015 2 Express Miles $104.99 104.99 y $65.00 209.98 Fullsize/Standard 352 04-10-2015 04-11-2015 1 Credit Card $84.99 84.99 y $65.00 169.98 Fullsize/Standard 356 04-11-2015 4/17/2015 6 Express Miles $84.99 84.99 y $65.00 169.98 Compact/Midsize 276 04-11-2015 4/14/2015 3 Credit Card $76.49 76.49 n 0 152.98 Compact/Midsize 277 04-11-2015 4/15/2015 4 Rewards $76.49 0 n 0 76.49 SUV/Minivan 554 04-10-2015 04-12-2015 2 COD $104.99 0 y $65.00 104.99 Green Collection 990 04-11-2015 4/18/2015 7 Express Miles $49.99 49.99 n 0 99.98 Rental Rates Green Collection $49.99 Compact/Midsize $76.49 Fullsize/Standard $84.99 SUV/Minivan $104.99 Discount for Express Miles or Rewards 20% Cost to Fill Tank $65.00 Median Rental Length Number of RentalsRelated Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.