Excel Access Exam Questions & Answers

Excel Access Exam
Questions & Answers

Write an Excel formula in cell 'Customer Usage'!I26 to determine the charges for any additional
features that have been added to this phone service. - ANSWERS=SUMIF('Addtl Features'!
D3:D8,TRUE, 'Addtl Features'!C3:C8)



Write an Excel formula in cell Statistics!B3, to display the number of prime-time calls and
unlimited calls made. - ANSWERS=COUNTIF('Customer Usage'!H4:H24,"Prime") +

COUNTIF('Customer Usage'!H4:H24,"UNAW")



Write an Excel formula in cell Statistics!B2, to display the total number of calls and texts made. -
ANSWERS=COUNT('Customer Usage'!A4:A24)



Write an Excel formula in cell Statistics!B4, to display the total base charges for all Prime Time
calls that were greater than 60 minutes and the base charge for all texts. (Prime Time Calls>60 +
Texts) - ANSWERS=SUMIFS('Customer Usage'!I4:I24,

'Customer Usage'!H4:H24,"Prime",

'Customer Usage'!F4:F24,">60") +

SUMIF('Customer Usage'!F4:F24,"Text", 'Customer Usage'!I4:I24)



Write an Excel formula in cell Statistics!B6, to determine (T/F) if at least one call or text was In-
Network. - ANSWERS=OR('Customer Usage'!G4:G24)



Write an Excel formula in cell 'Customer Usage'!K4, which can be copied down, to determine
(T/F) if the corresponding call was an in network text message OR the call or text message was
not in the network and was Prime time. - ANSWERS=OR(AND(F4="Text",G4),

AND(NOT(G4),H4="Prime"))

, Write an Excel formula in cell 'Addtl Features'!D9 to determine (T/F) if this person has at least
two Convenience features and at least one Security feature included in their phone plan. Write
this answer so that if the cell references change, the answer will still be correct. -
ANSWERS=AND(COUNTIFS(D3:D8,TRUE,B3:B8,"C")>=2,COUNTIFS(D3:D8,TRUE,B3:B8,"S")>=1)



Write an Excel formula in cell 'Customer Summary'! B12 to determine the most expensive call or
text made (use the base rate column) during this billing cycle. This formula should be copied
down to determine the 2nd most expensive call or text and the third most expensive call or text.
- ANSWERS=LARGE('Customer Usage'!I$4:I$24,A12)



Write an Excel formula in cell 'Customer Usage'!J4, which can be copied down, to determine
(T/F) if the corresponding base rate of the call or text was greater than or equal to $8.00. -
ANSWERS=I4 >= 8



Write an Excel formula to determine the payment on a new car. The selling price of the car is
$25,000 with 10% down payment. The annual interest rate is 3.5% compounded monthly. It will
take you 5 years to pay off the loan. No cell references with this one. -
ANSWERS=PMT(0.035/12,12*5, 22500, 0,0)



Write an Excel formula to determine the number of years it would take you to pay off a $25,000
loan at 6.5% annual interest compounded monthly. You will make monthly payments of $350.
No cell references with this one. - ANSWERS=NPER(.065/12,-350,25000,0,0)/12



Write an Excel formula in cell 'Customer Usage'!I4, which can be copied down, to calculate the
total base charges for the corresponding call made. Remember that text messages are
calculated differently than phone calls. (Text messages are denoted with the word "Text" in
column F of the 'Customer Usage' worksheet.) - ANSWERS=IF(F4="Text",'Base Rates'!
B$6,VLOOKUP(H4, 'Base Rates'!A$3:C$5,IF(G4,2,3),FALSE)*F4)



Write an Excel formula in cell 'Customer Usage'!I27 to calculate the Federal Universal Service
Tax. - ANSWERS=HLOOKUP(COUNTA(E4:E24),Tax,2,TRUE)