CGS 2518 TEST REVIEW QUESTIONS
WITH CORRECT ANSWERS
What function returns the relative position of an item from a list? - Answer-=MATCH ()
Using the Pricing worksheet, write a formula in cell C13 to determine the total cost of
making copies for this order (275). Write the formula so that it works when copied into
cells C14:C15.
Pricing Worksheet
A/B/C/D
1: (A)/Pricing Sheet for Copies
2: (A)/Number of Copies/Price per Copy
3: (A)/1/$0.10
4: (A)/10/0.09
5: (A)/25/0.08
6: (A)/50/0.07
7: (A)/100/0.05
8: (A)/500/0.03
9: (A)/1000/0.02
10:
11:
12: Order #/Number of Copies/Total Cost/Delivery Cost
13: 1/275/C13/D13
14: 2/3/C14/D14
15: 3/2500/C15/D15 - Answer-=PRODUCT(VLOOKUP(B13,$B$3:$C$9,2,TRUE),B13)
The delivery charges used in the blank Delivery worksheet are as follows:
For orders under $10, there is a $6 delivery fee
For orders at least $10 but less than $40, there is a $7.50 delivery fee
For orders over $40, delivery is free of charge.
Create a lookup table in the Delivery worksheet, so that you can use a horizontal
function to calculate the delivery cost for each order. - Answer-Delivery Worksheet
A/B/C/D
1: Delivery Charges:
2: Order Price:/$0/10/$40
3: Delivery Fee:/$6/$7.50/$0
Write a formula in cell D13 in the Pricing worksheet to look up the correct delivery cost
using the lookup table from the Delivery worksheet. Write the formula so that it can be
copied down the column.
Pricing Worksheet
, A/B/C/D
1: (A)/Pricing Sheet for Copies
2: (A)/Number of Copies/Price per Copy
3: (A)/1/$0.10
4: (A)/10/0.09
5: (A)/25/0.08
6: (A)/50/0.07
7: (A)/100/0.05
8: (A)/500/0.03
9: (A)/1000/0.02
10:
11:
12: Order #/Number of Copies/Total Cost/Delivery Cost
13: 1/275/C13/D13
14: 2/3/C14/D14
15: 3/2500/C15/D15
Delivery Worksheet
A/B/C/D
1: Delivery Charges:
2: Order Price:/$0/$10/$40
3: Delivery Fee:/$6/$7.50/$0 - Answer-=HLOOKUP(C13,Delivery!$B$2:$D$3,2,TRUE)
Explain the significance of the difference between lookup tables sorted in ascending
versus descending or no order. - Answer-The first row of a lookup table must be sorted
in ascending order in order to retrieve the correct result when using the range_lookup
value TRUE, for an approximate match. If not, the result it retrieves could be incorrect.
Function to calculate the value at the end of a financial transaction - Answer-FV
Function to calculate the interest percentage per period of a financial transaction -
Answer-RATE
Function to calculate the value at the beginning of a financial transaction - Answer-PV
Function to calculate the number of compounding periods in a financial transaction -
Answer-NPER
Function to calculate periodic payments into or out of a financial transaction - Answer-
PMT
Use a 0 for this argument to indicate that interest will be paid at the end of each
compounding period - Answer-Type
This type of interest is calculated based on original principal regardless of the previous
interest earned - Answer-Simple Interest
WITH CORRECT ANSWERS
What function returns the relative position of an item from a list? - Answer-=MATCH ()
Using the Pricing worksheet, write a formula in cell C13 to determine the total cost of
making copies for this order (275). Write the formula so that it works when copied into
cells C14:C15.
Pricing Worksheet
A/B/C/D
1: (A)/Pricing Sheet for Copies
2: (A)/Number of Copies/Price per Copy
3: (A)/1/$0.10
4: (A)/10/0.09
5: (A)/25/0.08
6: (A)/50/0.07
7: (A)/100/0.05
8: (A)/500/0.03
9: (A)/1000/0.02
10:
11:
12: Order #/Number of Copies/Total Cost/Delivery Cost
13: 1/275/C13/D13
14: 2/3/C14/D14
15: 3/2500/C15/D15 - Answer-=PRODUCT(VLOOKUP(B13,$B$3:$C$9,2,TRUE),B13)
The delivery charges used in the blank Delivery worksheet are as follows:
For orders under $10, there is a $6 delivery fee
For orders at least $10 but less than $40, there is a $7.50 delivery fee
For orders over $40, delivery is free of charge.
Create a lookup table in the Delivery worksheet, so that you can use a horizontal
function to calculate the delivery cost for each order. - Answer-Delivery Worksheet
A/B/C/D
1: Delivery Charges:
2: Order Price:/$0/10/$40
3: Delivery Fee:/$6/$7.50/$0
Write a formula in cell D13 in the Pricing worksheet to look up the correct delivery cost
using the lookup table from the Delivery worksheet. Write the formula so that it can be
copied down the column.
Pricing Worksheet
, A/B/C/D
1: (A)/Pricing Sheet for Copies
2: (A)/Number of Copies/Price per Copy
3: (A)/1/$0.10
4: (A)/10/0.09
5: (A)/25/0.08
6: (A)/50/0.07
7: (A)/100/0.05
8: (A)/500/0.03
9: (A)/1000/0.02
10:
11:
12: Order #/Number of Copies/Total Cost/Delivery Cost
13: 1/275/C13/D13
14: 2/3/C14/D14
15: 3/2500/C15/D15
Delivery Worksheet
A/B/C/D
1: Delivery Charges:
2: Order Price:/$0/$10/$40
3: Delivery Fee:/$6/$7.50/$0 - Answer-=HLOOKUP(C13,Delivery!$B$2:$D$3,2,TRUE)
Explain the significance of the difference between lookup tables sorted in ascending
versus descending or no order. - Answer-The first row of a lookup table must be sorted
in ascending order in order to retrieve the correct result when using the range_lookup
value TRUE, for an approximate match. If not, the result it retrieves could be incorrect.
Function to calculate the value at the end of a financial transaction - Answer-FV
Function to calculate the interest percentage per period of a financial transaction -
Answer-RATE
Function to calculate the value at the beginning of a financial transaction - Answer-PV
Function to calculate the number of compounding periods in a financial transaction -
Answer-NPER
Function to calculate periodic payments into or out of a financial transaction - Answer-
PMT
Use a 0 for this argument to indicate that interest will be paid at the end of each
compounding period - Answer-Type
This type of interest is calculated based on original principal regardless of the previous
interest earned - Answer-Simple Interest
