Question

Health & Fitness Gym
Date Prepared:
Client	Membership	Cost	Locker	Annual Total	Years	Total Due	Down Payment	Balance	Monthly Payment
Andrews	Deluxe		Yes		1
Baker	Individual		Yes		2
Carter	Family		No		3
Dudley	Deluxe		No		2
Evans	Deluxe		Yes		3
Foust	Individual		No		1
Gardner	Individual		No		2
Hart	Individual		No		3
Ivans	Individual		Yes		3
Totals
Membership	Cost	Down Payment		Summary Statistics
Deluxe	$           575	$      250		Number of New Members
Family	$        1,500	$      700		Lowest Monthly Payment
Individual	$           300	$      150		Average Monthly Payment
				Maxium Monthly Payment
Locker Fee	$            75			Median Monthly Payment
Interest Rate	5.75%
Months Per Year	12

6 Insert a vertical lookup function in cell H5 to display the amount of down payment for the first client based on the membership type.

7 Insert a formula in cell I5 to calculate the balance due on the membership. Copy the formulas in columns H and I for the rest of the clients.

8 Insert the PMT function in cell J5 to calculate the first client’s monthly payment, using appropriate relative and absolute cell references. Copy the formula down the column.

9 Calculate totals for Annual Total, Total Due, Down Payment, Balance, and Monthly Payment on row 14.

Answer 1

Solution:

6.  =VLOOKUP(B5,$A$18:$C$20,3,TRUE)

Vlookup formula in excel is used as above, write absolute and reference value as per the need.

7.  =(VLOOKUP($B$5:$B$13,$A$18:$B$20,2,TRUE)-H5)

Just because the excel sheet and the question do not have the value on Annual Total and Total Dues, the formula for this cell is as above.

After writing the formula, just copy the formula and paste into desired cells.

8. =PMT($B$23/$B$24,F5*$B$24,-H5)

It calculates monthly payment as EMI for the number of years mentioned in the years column. ow copy and paste the formula in desired cells.

9. =SUM(E5:E13) write this formula in E14 then copy this formula and paste into G14, H14, I14, J14

the final sheet look like as under.