Formulas and Functions
In the following project, you will perform preliminary work on the gym worksheet. You will then calculate cost, annual total, and total due. You will also determine the down payment and balance of gym membership. Your last steps will be to calculate the monthly payment and finalized the workbook.
For the purpose of grading the project you are required to perform the following tasks:
Download and open the file namedexploring_e02_grader_h1.xlsx, and then save the file asexploring_e02_grader_h1_LastFirst. Click OK in the message regarding the circular reference.
Hint: LastFirst refers to your name.
Create a named range for cells A18:C20 namedMembership.
Hint: Select the range A18:C20. Click in the Name box, typeMembership and then press ENTER. An alternate way of creating a named range is by using the Name Manager, located within the Formulas ribbon.
Insert a function to enter the current date in cell B2.
Hint: - In cell B2, type =TODAY() and then press ENTER. This Excel function is an example of one that requires no arguments (parameters). However, most Excel functions require one or more arguments.- Ensure the function returns only the date, and not both the date and time.
In cell C5 insert a function to display the basic annual membership cost of the first client.
Hint: - You will perfect this function for the first member (Andrews) in row 5, and then in a later step, replicate this function down column C to apply it to the other gym members. When a formula/function is going to be replicated to other locations, you should always consider whether it is appropriate to use absolute cell referencing (ask yourself why would this be the case?).- While the Table_array argument can be expressed as a range, in this case, use the named range: Membership.- The Table_array argument must be in ascending order - please check to ensure it is.- The range of cells making up the Table_array argument do not include any column headings.- The fourth argument, Range_lookup, controls whether the function will find the closest possible match in the table or whether it will find an exact match. The function should look for an 'exact' match for the client’s membership type to determine the cost.
Insert a function in cell E5 to calculate total amount. The function should add the cost of membership plus, if applicable, the locker fee. The locker column displays Yes for clients that rent lockers.
Hint: - You will perfect this function for the first member (Andrews) in row 5, and then in a later step, replicate this function down column E to apply it to the other gym members. When a formula/function is going to be replicated to other locations, you should always consider whether it is appropriate to use absolute cell referencing.- When dealing with text strings within functions, the MS convention is to use "" to wrap the text string - e.g., "GSU"- Arguments within functions are separated by commas.
In cell G5 calculate the total due based on the annual total and years of membership in column F.
Hint: Use a formula (no function is required).
Copy the three formulas down their respective columns.
Hint: - Using the fill handle to replicate the formulas down to other clients is problematic, if it also replicates the formatting featured in row 5 (which is clearly different from the formatting featured in rows 6 through 13).
Insert a function in cell H5 to display the amount of down payment for the first client.
Hint: - The down-payment data is shown in the lower part of the worksheet.- This function should look up the amount of the down payment based on membership type. The function should look for an exact match.
Locate and correct the circular reference for the balance in cell I5. The balance should be calculated as the difference between total due and the down payment.
Hint: On the FORMULAS tab, in the Formula Auditing group, click the Error Checking arrow. In cell I5, change the formula to =G5-H5 and then press ENTER.