CGS 2518 FINAL EXAM QUESTIONS
WITH COMPLETE SOLUTIONS
A worksheet contains addresses in column C. The addresses use commas after the
street, city, and state, such as 129 Elm Street, Burlington, NC, 27215. Column D
contains the phone number. You use Text to Columns to divide the addresses into
multiple columns using comma delimiters. You successfully divide the addresses into
four columns. What happens to the phone numbers in column D?
(a)The phone numbers are overwritten with city names.
(b)The phone numbers are converted to commas.
(c)Excel phone number column is moved to the right side of all the columns containing
the parts of the addresses.
(d)The phone number column is moved to the left of the original address column. -
Answer-The phone numbers are overwritten with city names
Which function identifies and returns the position of a value within an array?
(a)ARRAY
(b)INDEX
(c)MATCH
(d)CHOOSE - Answer-MATCH
What function should you use to look up a specified value within a database at the
intersection of a specific row and column position?
(a)CHOOSE
(b)INDEX
(c)MATCH
(d)TRANSPOSE - Answer-INDEX
A column contains phone numbers such as (801)-555-1234. What function should you
use to extract the last four digits?
(a)LEFT
(b)RIGHT
(c)MID
(d)LEN - Answer-RIGHT
A column contains names in uppercase, which is hard to read. Which function displays
the names where the first letter of each word is capitalized?
(a)INITIALCAPS
(b)LOWER
, (c)PROPER
(d)TITLECASE - Answer-PROPER
The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5?
(a)APR
(b)Periodic interest rate
(c)Loan amount
(d)Number of payment periods - Answer-Periodic interest rate
What is the keyboard shortcut to create an absolute reference?
(a)F2
(b)F3
(c)F4
(d)Alt - Answer-F4
The date 8/3/2021 is stored in cell C1. What function is used to extract just 3?
(a)=YEAR(C1)
(b)=MONTH(C1)
(c)=DAYS(B1,C1)
(d)=DAY(C1) - Answer-=DAY(C1)
What function would you use to calculate the total interest paid for the first year of a
mortgage?
(a)CUMIPMT
(b)PMT
(c)PPMT
(d)CUMPRINC - Answer-CUMIPMT
You constructed a loan amortization table with beginning balance, monthly payment,
interest paid, principal repayment, and ending balance columns. If all the formulas are
correct, which is the only column that shows an increase in value throughout the term of
the loan?
(a)Beginning Balance
(b)Monthly Payment
(c)Interest Paid
(d)Principal Repayment - Answer-Principal Repayment
What function would you use to calculate the total number of periods in a loan or
investment?
(a)NPER
WITH COMPLETE SOLUTIONS
A worksheet contains addresses in column C. The addresses use commas after the
street, city, and state, such as 129 Elm Street, Burlington, NC, 27215. Column D
contains the phone number. You use Text to Columns to divide the addresses into
multiple columns using comma delimiters. You successfully divide the addresses into
four columns. What happens to the phone numbers in column D?
(a)The phone numbers are overwritten with city names.
(b)The phone numbers are converted to commas.
(c)Excel phone number column is moved to the right side of all the columns containing
the parts of the addresses.
(d)The phone number column is moved to the left of the original address column. -
Answer-The phone numbers are overwritten with city names
Which function identifies and returns the position of a value within an array?
(a)ARRAY
(b)INDEX
(c)MATCH
(d)CHOOSE - Answer-MATCH
What function should you use to look up a specified value within a database at the
intersection of a specific row and column position?
(a)CHOOSE
(b)INDEX
(c)MATCH
(d)TRANSPOSE - Answer-INDEX
A column contains phone numbers such as (801)-555-1234. What function should you
use to extract the last four digits?
(a)LEFT
(b)RIGHT
(c)MID
(d)LEN - Answer-RIGHT
A column contains names in uppercase, which is hard to read. Which function displays
the names where the first letter of each word is capitalized?
(a)INITIALCAPS
(b)LOWER
, (c)PROPER
(d)TITLECASE - Answer-PROPER
The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5?
(a)APR
(b)Periodic interest rate
(c)Loan amount
(d)Number of payment periods - Answer-Periodic interest rate
What is the keyboard shortcut to create an absolute reference?
(a)F2
(b)F3
(c)F4
(d)Alt - Answer-F4
The date 8/3/2021 is stored in cell C1. What function is used to extract just 3?
(a)=YEAR(C1)
(b)=MONTH(C1)
(c)=DAYS(B1,C1)
(d)=DAY(C1) - Answer-=DAY(C1)
What function would you use to calculate the total interest paid for the first year of a
mortgage?
(a)CUMIPMT
(b)PMT
(c)PPMT
(d)CUMPRINC - Answer-CUMIPMT
You constructed a loan amortization table with beginning balance, monthly payment,
interest paid, principal repayment, and ending balance columns. If all the formulas are
correct, which is the only column that shows an increase in value throughout the term of
the loan?
(a)Beginning Balance
(b)Monthly Payment
(c)Interest Paid
(d)Principal Repayment - Answer-Principal Repayment
What function would you use to calculate the total number of periods in a loan or
investment?
(a)NPER
