Spreadsheet Exercises Solutions

Exercise Windows solution

ex5solu

Use the Back button to return to the Exercises

Exercise Range Names Solution

ex6solu

Use the Back button to return to the Exercises

Exercise Functions Solution

Question	Solution
=ABS(-7.567)	7.567
=INT(7.567)	7
=ROUND(7.567,2)	7.57
Round off the following number to 1 decimal place -34.5632.	=Round(-34.5632,1)
=SQRT(16)	4
=SQRT(ABS(-64))	8
What is the square root of 234	=Sqrt(234)
=RAND()	=RAND()
Use the RAND function to generate a number between 0 and 10	=Rand()*10
Use the RAND function to generate a whole number between 1 and 10	=Int(Rand()*10)+1
Use a combination of functions to get a random number between 3 and 10	=Rand()*7+3

Use the Back button to return to the Exercises

Exercise STATISTICAL FUNCTIONS Solutions

Question	Solution
Determine the result of the following AVERAGE(4,6,10,12) SUM(5,6,7) COUNT(2,2,2,2)	8 18 4
With cell A1 = 36 and B3 =45, try the following AVERAGE(4,6,A1) SUM(A1,B3) COUNT(A1,B3,C3,4)	15.33 81 3
For the following exercises create a list of 20 numbers in a column. Get the average, maximum value and minimum value of the list. Total the list Use the Count function to count the number of entries in list	Use average(range), max(range) and min(range) functions. Use the Sum(range) Use the Count(range) function.

Use the Back button to return to the Exercises

Exercise Logical functions - Solutions

a) Cell C5 should contain the function
=if(A1<=100,100,300)

b) Cell C5 should contain the function
=if(A1<100,100,if(A1=100,200,300))

c) Cell C5 should contain the function

=if(A1>1000,400,if(A1>100,300,if(A1=100,200,100)))

d) Cell C4 should contain the function

=if(and(A4>0,B4>0),100,0)

e) Below is part of row 5 showing suitable formula

	F	G	H
Row 5	=IF(D5=1,E5," ")	=IF(D5=2,E5," ")	=IF(D5=3,E5," ")

Use the Back button to return to the Exercises

f) Exercise 3 as a LOOKUP table.

ex93solu

Cell C5 contains the simpler function =Vlookup(A1,A3:B7,2)

g) no solution available

Use the Back button to return to the Exercises

Exercise Winelist Database Solutions

a) Sort the list by producer (no solution provided)
b) Query for age = 1976
There is only one wine, the REDMAN CABERNET of which there are 3 bottles

c) Query for age = 1980
Again only one wine the WYNS CABERNET

d) Query for the Producer = Cullens
CULLENS TAS FINE WINES DRY WHITE 1984 WEST AUST. 12.17 25.80 11

e) Query for Dry Reds
There are 20 wines which are dry reds

f) Query for wines that are 1984 and Dry whites
There are 8 1984 wines which are dry whites

g) Query for wines that cost >$10.00 and older than 1978
There are two wines
REDMAN CABERNET DRY RED 1976 26.86 3

PENFOLDS ST.HENRI CLARET DRY RED 1973 12.11 4

h) Fred Derf is anxious to impress his friend and wants any wine that is priced at greater than $30 or is a champagne. Can you help ?

dearwine

i) A customer wants either 12 bottles of Dry White or 12 bottles of Dry red, can you provide it.

(hint: the quantity we have on hand can be 12 or more!)

There is a range of 17 wines that meet the requirements.

j) Create a subset of 1981 wines elsewhere on your worksheet

To save space only a few fields are shown in this solution.

wine1981

Use the Back button to return to the Exercises

Macro Exercises

a. Write a macro to write your name in a cell

Macro1 Solution exercise 1

 Sub Macro1()
        ActiveCell.FormulaR1C1 = "Mike"
 End Sub

b. Write a macro to change the width of a column to 15 characters.(no solution provided)

c. Create a macro that takes the text in any cell and formats it as 20 point and Bold.

Macro2 Solution exercise 3

Sub Macro2()
       With Selection.Font
                .FontStyle = "Bold"
                .Size = 20
       End With
End Sub

Use the Back button to return to the Exercises

d. Create a button to execute the previous macro (no solution provided)

e. Join the macro that writes your name in a cell to the macro that formats text to 20 point bold.

You can use cut and paste to join the two macros together.


Sub Macro5()
    ActiveCell.FormulaR1C1 = "Mike"
    With Selection.Font
             .FontStyle = "Bold"
             .Size = 20
     End With
End Sub

Use the Back button to return to the Exercises

f. Create Macros and Buttons that allows a user to jump between Sheets 1 and Sheet 2(no solution provided)

g. Use a List box to display the states of Australia.
Selection of a list item should display the name of the State capital in cell.

liststat

Use the Back button to return to the Exercises

Exercise - Dialog Box Solutions

12.1

ex10dave

12.2 Produce the following dialog box.

ex121sol

Sub Macro2()
response = MsgBox("A Two Button Box", 1, "Buttons Galore !")
End Sub

Use the Back button to return to the Exercises

12.3 Create a dialog box that contains

the Buttons Yes and No.
the Title "Exercises" and
the prompt "Are you ready for the next exercise"

(no solution is available for this question)

12.4 Make the dialog box in question 2, display the Information Icon.

Sub Macro2()
response = MsgBox("A Two Button Box", 65, "Buttons Galore !")
End Sub

12.5 Extend exercise 3 by causing Excel to display in cell A1 the text

'You pressed the Yes button'
'You pressed the No button'

depending on which button was pressed.

Solution

Sub macro1()
Range("A1").Select
response = MsgBox("press Yes or No", 4, "exercise 4")
If response = 6 Then ActiveCell.Formula = "you pressed a Yes"
If response = 7 Then ActiveCell.Formula = "you pressed a No"
End Sub

Use the Back button to return to the Exercises

Auto Open Auto Close Solutions

There are no solutions provided for these exercises

13.1 Create a macro that turns off the following:
gridlines, row and column headings, status bar and changes the colour
(Hint:Use the Tools Options View tab - do it manually first if uncertain)

13.2 Write another macro to reverse the above.

13.3 Save the macro in exercise 13.1 under the name auto_open.
Save the macro in exercise 13.2 under the name auto_close.
Save the worksheet and close the book.
Now reopen the book.

Use the Back button to return to the Exercises