Question 1
Consider the following Excel screenshot:

Cells C10:C13 are a named range called “Province”.
Which of the following is the correct entry in the ‘Source’ field in the data validation dialogue box to
display the drop-down list in cell F4 if cells C10:C13 are a named range called “Province”?
A) Ontario,Quebec,Alberta,British Columbia
B) =C10:C13
C) Province
D) ={Ontario,Quebec,Alberta,British Columbia}


Questions 2, 3
Consider the following Excel screenshot:


Which of the following is the correct formula to look up 2016 Taxes in cell O7?
A) =VLOOKUP(N7,B4:G12,7, FALSE)
B) =HLOOKUP(O6,B5:K12,7,FALSE)
C) =INDEX(G4:K11,MATCH(O6,G4:H11,0),MATCH(N7,G4:K11,0))
D) =INDEX(B4:K11,MATCH(N7,B4:B11,0),MATCH(O6,B4:K4,0))
If cell G4’s data entry is 2015, what number format is being used in G4?
A) General\E
B) 0.00 + “E”
C) #,##0 + “E”
D) _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)


Questions 4, 5
What is the INDIRECT equivalent of the formula ‘=COUNTIF(‘Task1’!A3:A25,”>0”) if the string
‘Task1’ is contained in cell A1?
A) =INDIRECT(“COUNTIF(‘”&A1&”’!A3:A25,”>0”))
B) =COUNTIF(INDIRECT(“’”&A1&”’!A3:A25),”>0”)
C) =COUNTIF(“’”&INDIRECT(A1)&”’!A3:A25,”>0”))
D) =COUNTIF(“’”&A1&”’!”&INDIRECT(“A3:A25,”>0”))

Which of the following is the correct formula to skip a weekend day in a column of consecutive dates
(assuming it is written in cell A7)?
A) =IF(OR(WEEKDAY(A6+1)=1,WEEKDAY(A6+1)=7),A6+3,A6+1)
B) =IF(WEEKDAY(A6+1)=1,A6+2,IF(WEEKDAY(A6+1)=7),A6+3,A6+1)
C) =IF(WEEKDAY(A6+1)=7,A6+3,A6+1)
D) =IF(AND(WEEKDAY(A6+1)=1,WEEKDAY(A6+1)=7),A6+3,A6+1)


Consider the following lines of code for a NetPresentValue function. This functions returns the NPV of a
series of cash flows, including the cash flow at time 0, provided in an Excel range of cells.
------------------
1 Function NetPresentValue(GivenRate As Double, GivenRange As Range)
2 Dim NPV As _____
3 NPV = 0
4
5 For N = 1 To _____
6 NPV = _____
7 Next N
8
9 End Function
------------------
Question 6
Fill in the blank in Line 2.
Question 7
Fill in the blank in Line 5.
Question 8
Fill in the blank in Line 6.
Question 9
What should Line 8 state for the function to return the NPV of the provided series of cash flows?
Question 10
If you want the function to return the net present value of cash flows, excluding the cash flow at time 0,
i.e. like the built-in NPV function, how would you rewrite Lines 5 to 7?

Answers
1. B
2. D
3. A
4. B
5. C
6. Double
7. GivenRange.Count
8. NPV = NPV + GivenRange(N) / ((1 + GivenRate) ^ (N - 1))
9. NetPresentValue = NPV
10. See below
For N = 1 To (GivenRange.Count – 1)
NPV = NPV + GivenRange(N + 1) / ((1 + GivenRate) ^ N)
Next N


欢迎咨询51作业君