for Beginners

ISBN: | 978-981-4627-10-8 |
---|---|

Publisher: | McGraw Hill Education |

Year: | 2014 |

Pages: | 323 |

# Answers of Review Questions

Answers of review questions up to chapter 8 have been released. The answers for the rest of the chapters will be released later.

You can click on the '+' button to show the review question answers of a particular chapter.

### Chapter 1. Introduction to Excel

**Review Question 1.1**-`5000%`

**Review Question 1.2**-`65`

**Review Question 1.3**-Let's give names to cell A3 and cell F1. Assuming the names of cell A3 is

`MyCell1`

and cell F1 is`MyCell2`

then the revised formula will be:`=MyCell1-B$3+$C3/MyCell2`

**Review Question 1.4**-`=B2/SUM($B$2:$B$13)`

**Review Question 1.5**-`=B2/all_rainfall`

`=B2/SUM(all_rainfall)`

### Chapter 2. More on Cell Formulas

**Review Question 2.1**-`=A2 & "s"`

*OR*`=CONCATENATE(A2,"s")`

**Review Question 2.2**-`=LEFT(A2,LEN(A2)-1)`

**Review Question 2.3**-Assuming the formula is not located in row 3 or column C, the result of the formula is

`20`

`16`

.**Review Question 2.4**-D

**Review Question 2.5**-`=AND(OR(B3="iOS",B3="Android"),AND(B1<>"black",B2<3600), AND(OR(B4=TRUE,B5=TRUE),B6=TRUE))`

**Review Question 2.6**-`=AND(B2>B3+B4,B5)`

**Review Question 2.7**-`=SUBSTITUTE(B11,A5,B5)`

**Review Question 2.8**-G

**Review Question 2.9**-Yes.

**Review Question 2.10**-`I WILL NOT do my homework`

**Review Question 2.11**-`hats`

**Review Question 2.12**-`=MAX(F2:F50)-MIN(F2:F50)`

**Review Question 2.13**-`67.5`

**Review Question 2.14**-`67.5`

Yes, they are the same.**Review Question 2.15**-`TRUE`

**Review Question 2.16**-`=money_available>total_cost`

`=money_available>=total_cost`

**Review Question 2.17**-`=IF(money_available>total_cost,TRUE)`

`=IF(money_available>=total_cost,TRUE)`

*OR*`=IF(money_available>total_cost,TRUE,FALSE)`

`=IF(money_available>=total_cost,TRUE,FALSE)`

*OR*`=IF(money_available<total_cost,FALSE,TRUE)`

`=IF(money_available<=total_cost,FALSE,TRUE)`

**Review Question 2.18**-`=IF(money_available>total_cost,"Great!","Oh no!")`

`=IF(money_available>=total_cost,"Great!","Oh no!")`

*OR*`=IF(money_available<total_cost,"Oh no!","Great!")`

`=IF(money_available<=total_cost,"Oh no!","Great!")`

**Review Question 2.19**-`=IF(B5<=44,"F",IF(B5<=54,"D",IF(B5<=74,"C",IF(B5<=89,"B","A"))))`

*OR*`=IF(B5>89,"A",IF(B5>74,"B",IF(B5>54,"C",IF(B5>44,"D","F"))))`

**Review Question 2.20**-Yes.

**Review Question 2.21**-B

**Review Question 2.22**-`21`

**Review Question 2.23**-The following answers assume that the data begins on the very first row. In this case we can use

`ROW()`

to know what the current row is.`=IF(MOD(ROW(),30)=0,"Pay day!","")`

*OR*`=IF(MOD(ROW(),30)>0,"","Pay day!")`

*OR*`=IF(NOT(MOD(ROW(),30)),"Pay day!","")`

*OR*`=IF(MOD(ROW(),30),"","Pay day!")`

**Review Question 2.24**-`=IF(MOD(ROW(),30*12)=0,"Double day pay!",IF(MOD(ROW(),30)=0,"Pay day!",""))`

*OR*`=IF(NOT(MOD(ROW(),30*12)),"Double day pay!",IF(NOT(MOD(ROW(),30)),"Pay day!",""))`

**Review Question 2.25**-`=IF(MOD(A6,2)=0,"Even","Odd")`

*OR*`=IF(MOD(A6,2)=1,"Odd","Even")`

*OR*`=IF(NOT(MOD(A6,2)),"Even","Odd")`

*OR*`=IF(MOD(A6,2),"Odd","Even")`

**Review Question 2.26**-`=IF(MOD(A4,4)=0,"LEAP YEAR!","Normal year")`

*OR*`=IF(MOD(A4,4)>0,"Normal year","LEAP YEAR!")`

*OR*`=IF(NOT(MOD(A4,4)),"LEAP YEAR!","Normal year")`

*OR*`=IF(MOD(A4,4),"Normal year","LEAP YEAR!")`

**Review Question 2.27**-**Part A**`=INT(A2/60)`

**Part B**`=MOD(A2,60)`

**Review Question 2.28**-`=HYPERLINK("https://www.google.com/search?q=" & SUBSTITUTE(A5,"+","%2B"),"Click to search for " & A5)`

### Chapter 3. Useful Excel Tools

**Review Question 3.1**-`=investment*([@Year]-start_year)`

*OR*`=investment*([Year]-start_year)`

Please be reminded that you need to use the brackets, i.e. ( ), here because of the operator precedence.

**Review Question 3.2**-The fastest way is to select any cell in column B and then press the 'Sort Largest to Smallest' button

**Review Question 3.3**-You can use the Sort window. Add each column into the Sort window by adding a new level. Use the order of Universities (column B), Departments (column C), Students (column E), Countries (column A) and Professors (column D) inside the window. The sort order of the first three levels is alphabetical order and of the last two levels is reverse alphabetical order.

Here is an example of what you can use in the Sort window:

**Review Question 3.4**-E

**Review Question 3.5**-The Advanced Filter

**Review Question 3.6**-Chan Tai Man, Chan Yat Ming and Chan Yi Ming

**Review Question 3.7**-Cell B4 and cell B5

### Chapter 4. Getting Started with Excel VBA

**Review Question 4.2**-Sub Macro2() ' 'Macro2 Macro ' Selection.Font.Italic = True End Sub

### Chapter 5. Basic VBA Programming

**Review Question 5.1**-The

`MsgBox`

command is used to show 'output', while the`InputBox`

command is used to get 'input'.**Review Question 5.2**-Assume that we use the following code to read the integer from an input box:

Dim Age As Integer Age = InputBox("What is your age?")

If the user enters an integer, e.g. 2, 18 or 25, the integer will be stored in the

`Age`

variable successfully. However, if the user enters anything other than an integer, the code will not store the value correctly and the code may even crash (stop working suddenly).**Review Question 5.3**-No

**Review Question 5.4**-No

**Review Question 5.5**-No. It is because the entered number will be rounded to the nearest integer when it is stored in the integer variable

`Radius`

.**Review Question 5.6**-*Note: this question uses a function, which will be introduced in chapter 9.***Part A**The code produces a value of

`True`

in the variable`Assessment`

.**Part B**The function returns

`True`

for a male human or a female human. It returns`False`

otherwise.**Review Question 5.7**-`Range("B25")`

*OR*`ActiveSheet.Range("B25")`

*OR*`Cells(25, 2)`

*OR*`ActiveSheet.Cells(25, 2)`

*OR*`ActiveCell`

*OR*`Selection`

**Review Question 5.8**-You will see this:

**Review Question 5.9**-Yes

**Review Question 5.10**-The macro fills the content of the selected cells with the letter 'o'.

**Review Question 5.11**-The macro puts the text 'Here' into the four corners of the selected cell area.

### Chapter 7. Making Decisions in VBA

**Review Question 7.1**-The two pieces of VBA code will produce the same output after the same age is entered except for the number 18. If the input age is 18, VBA Code 1 will produce the

`MsgBox`

with the string`"Wow! You are an adult!"`

, but VBA Code 2 will produce nothing.**Review Question 7.2**-Sub ConvertEnglishNumber() ' Declare a variable to store the input Dim Number As Integer ' The input number Number = InputBox("Please enter a number:") ' Declare two variables to store the output Dim Result As String, QuotientStr As String ' Declare two variables to store the remainder (the unit digit) and ' quotient (the tens digit) Dim Remainder, Quotient As Integer ' Calculate the unit digit Remainder = Number Mod 10 ' Calculate the tens digit Quotient = (Number - Remainder) / 10 ' Check if the unit digit is equal to 1, 2, 3, ..., 9 If Remainder = 1 Then Result = "one" ElseIf Remainder = 2 Then Result = "two" ElseIf Remainder = 3 Then Result = "three" ElseIf Remainder = 4 Then Result = "four" ElseIf Remainder = 5 Then Result = "five" ElseIf Remainder = 6 Then Result = "six" ElseIf Remainder = 7 Then Result = "seven" ElseIf Remainder = 8 Then Result = "eight" ElseIf Remainder = 9 Then Result = "nine" End If ' Check if the tens digit is equal to 1, 2, 3, ..., 9 If Quotient = 1 Then ' Check if the input number is between 11 to 19 If Remainder = 0 Then Result = "ten" ElseIf Remainder = 1 Then Result = "eleven" ElseIf Remainder = 2 Then Result = "twelve" ElseIf Remainder = 3 Then Result = "thirteen" ElseIf Remainder = 4 Then Result = "fourteen" ElseIf Remainder = 5 Then Result = "fifteen" ElseIf Remainder = 6 Then Result = "sixteen" ElseIf Remainder = 7 Then Result = "seventeen" ElseIf Remainder = 8 Then Result = "eighteen" ElseIf Remainder = 9 Then Result = "nineteen" End If ElseIf Quotient = 2 Then QuotientStr = "twenty" ElseIf Quotient = 3 Then QuotientStr = "thirty" ElseIf Quotient = 4 Then QuotientStr = "fourty" ElseIf Quotient = 5 Then QuotientStr = "fifty" ElseIf Quotient = 6 Then QuotientStr = "sixty" ElseIf Quotient = 7 Then QuotientStr = "seventy" ElseIf Quotient = 8 Then QuotientStr = "eighty" ElseIf Quotient = 9 Then QuotientStr = "ninety" End If ' Check if the number is larger or equal to 20 If Number >= 20 Then ' If unit digit is not zero, insert the string for the tens digit in front If Remainder <> 0 Then Result = QuotientStr & "-" & Result Else Result = QuotientStr End If End If ' Output the convert English number to the message box MsgBox Result End Sub

### Chapter 8. Looping

**Review Question 8.1**-4

**Review Question 8.2**-`Do While...Loop`

and`Do Until...Loop`

**Review Question 8.3**-All of them. Even though

`Do While...Loop`

and`Do Until...Loop`

may not execute the loop content at all, the programmer can carefully use a condition which ensures that the loop will be executed at least once.Here is an example:

Sum = 0 Number = 1 While Number <= 10 Sum = Sum + Number Number = Number + 1 Wend

By ensuring the Number variable is smaller than 10 before entering the while loop, the loop will be executed at least once (the loop content will execute 10 times in this example.)

**Review Question 8.4**-Here is some example code created using a for loop:

Dim MoneyForTheFirstWeek As Single Dim MoneyForTheWeek As Single Dim MoneyForTheSemester As Single Dim Week As Integer MoneyForTheFirstWeek = 850 MoneyForTheSemester = 0 For Week = 1 To 16 If Week >= 1 And Week <= 4 Then MoneyForTheWeek = MoneyForTheFirstWeek ElseIf Week >= 4 And Week <= 13 Then MoneyForTheWeek = MoneyForTheWeek * 0.95 ElseIf Week = 14 Then MoneyForTheWeek = MoneyForTheFirstWeek + 200 Else MoneyForTheWeek = MoneyForTheFirstWeek * 0.66 End If MoneyForTheSemester = MoneyForTheSemester + MoneyForTheWeek Next Week MsgBox "The student will spend $" & MoneyForTheSemester & _ " on food across the entire semester."

### Chapter 9. Functions and Subroutines

**Review Question 9.1**-`Function`

**Review Question 9.2**-**Part A**Three (

*a*,*b*and*c*).**Part B**Yes.

**Part C**Yes because the function will need input parameters and it will return a value. For example, like this:

x = Quadratic(a, b, c)

**Review Question 9.3**-13

**Review Question 9.4**-25

### Chapter 10. GUI Handling

**Review Question 10.1**-A Form ComboBox

### Chapter 11. Sorting and Filtering in VBA

**Review Question 11.1**-`Range("A3:K12").Sort Key1:=Range("B5:K5"), Order1:=xlDescending, Orientation:=xlSortRows`

*OR*`Range("A3").Sort Key1:=Range("B5:K5"), Order1:=xlDescending, Orientation:=xlSortRows`

*OR*`Range("A3:K12").Sort Key1:=Range("B5"), Order1:=xlDescending, Orientation:=xlSortRows`

*OR*`Range("A3").Sort Key1:=Range("B5"), Order1:=xlDescending, Orientation:=xlSortRows`

*OR*`Range("A3:K12").Sort Key1:=Rows("5"), Order1:=xlDescending, Orientation:=xlSortRows`

*OR*`Range("A3").Sort Key1:=Rows("5"), Order1:=xlDescending, Orientation:=xlSortRows`

**Review Question 11.2**-**Part A**Range("L3") = "Average" For Row = 1 To 9 Cells(Row + 3, 12) = _ Application.WorksheetFunction.Average(Range("B4:K12").Rows(Row)) Next Row

*OR*Range("L3") = "Average" For Row = 4 To 12 Sum = 0 For Col = 2 To 11 Sum = Sum + Cells(Row, Col).Value Next Col Cells(Row, 12) = Sum / 10 Next Row

**Part B**`Range("A5:L12").Sort Key1:=Range("L5"), Order1:=xlDescending`

**Review Question 11.3**-**Review Question 11.4**-**Part A**In this answer, we assume the criteria is in cells

`D1:D2`

.`Range("A7:G207").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("D1:D2"), CopyToRange:=Range("J7")`

*OR*`Range("A7:G207").AdvancedFilter xlFilterCopy, Range("D1:D2"), Range("J7")`

**Part B**`Range("J7").AutoFilter 7, 5, xlTop10Items`

*OR*`Range("J7").AutoFilter Field:=7, Criteria1:=5, Operator:=xlTop10Items`

### Chapter 12. Cell Functions in VBA

**Review Question 12.1**-`=SUM(B2:D6) / AVERAGE(A:A) * VLOOKUP("Toys", E:E, 3, TRUE)`

**Review Question 12.2**-`Result = WorksheetFunction.CountIf(Range("A:A"), "<=50")`

**Review Question 12.3**-`Concatenate`

is not available in`WorksheetFunction`

. Reminded that not every cell function can be used by VBA through`WorksheetFunction`

. We can use`&`

to do concatenation like this:`Result = WorksheetFunction.CountIf(Range("A:A"), "<=" & Range("C5").Value)`

### Chapter 13. Object Oriented Programming

**Review Question 13.1**-Some possible attributes are

*maximum number of passengers*,*colour*,*engine capacity*and so on.Some possible methods are

*start engine*,*stop engine*,*accelerate*,*break*and so on.**Review Question 13.2**-2000

### Chapter 14. Recursion

**Review Question 14.1**-13 times

**Review Question 14.2**-3

**Review Question 14.3**-Function Factorial(ByVal n As Integer) If n > 1 Then Factorial = Factorial(n - 1) * n Else Factorial = 1 End If End Function

**Review Question 14.4**-2 times. It is because, if you look at the image, the tree will repeat itself in two branches.

### Chapter 15. Arrays

**Review Question 15.1**-~~C~~D