Introduction to Excel Formulae & Functions - Exercises - UCL HEP

Content
Task 1 Simple arithmetic Task 2 Using Formulae Task 3 Copying Formulae Task 4 Functions Task 5 Sum Function Task 6 Count Function Task 7 Precision as displayed Task 8 Absolute Referencing Task 9 Using Names Task 10 COUNTIF & SUMIF Task 11 Logical IF Task 12 Viewing Formulae
Further Exercises Exercise 1 Holiday Costs Exercise 2 Newspaper Sales Exercise 3 Newspaper Sales Exercise 4 Petty Cash Exercise 5 Confectionery Sales
These tasks and exercises accompany the Introduction to Excel
Formulae and Functions workbook produced by UCL Information
Systems. You will need to download files to accompany the
course from www.ucl.ac.uk/is/training/exercises.htm - click on
the Excel link to download the files.
Task 1 - Simple Arithmetic In this exercise you will create basic formulae involving simple
calculations on a pair of values. The sums involved are intentionally
simple to allow you to check that your answers are correct using a little
mental arithmetic. Enter the data shown opposite into a new blank Workbook. Leave the
cells containing the word formula empty for now: 1. Enter a formula to add together the contents of cells B3 and B4. Place
the result in B6. 2. Enter a formula to subtract the contents of cells B4 from B3. Place the
result in cell B7. 3. Enter a formula to multiply the contents of cell B3 by B4. Place the
result in cell B8. 4. Enter a formula to divide the contents of cell B3 by B4. Place the
result in B9. 5. Do a quick check that your answers are correct, then save the file as
maths.xls in the IntroFormulaeFunctions folder.
Task 2 - Using Formulae 1. Open the maths.xls Workbook created in the previous exercise. 2. Go to Sheet 2 and on a blank Worksheet try the following calculations: . There are 20 female and 30 male students in each of 10 groups. How
many students are there? . There are 23 black pens, 46 blue pens, and 11 pencils to be shared
among 10 students. How many writing implements would each student get? . 100 sandwiches were delivered for a lunch buffet, 30 were eaten by the
helpers before the delegates (6 from Oxford, 12 from Cambridge, and 17
from London) arrive. How many sandwiches would each delegate have? . Plane tickets are only available if you purchase them 45 days in
advance. If today is 5 September, and you want to travel on 26
October, how many days do you have to buy your tickets? . Your friend's birthday is the 14 June. If today is 24 May, how many
weeks do you have to buy a present?
Task 3 - Copying Formulae 1. Open the maths.xls Workbook created in the previous exercise. 1. Modify the Worksheet by adding two new sets of values as shown below in
cells C3, C4, D3, and D4 2. Copy each of the formulae in Column B to Columns C and D. 3. Click in cell C6 and check that the formula is correct (when you click
in the cell you will see the formula rather than the result). It should
be =C3+C4 4. Check that the copied formulae have done what you needed using a bit of
mental arithmetic then save and close the Workbook.
Task 4 - Functions 1. Open the summary.xls file in the IntroFormulaeFunctions folder. 2. Add a sixth column to the Worksheet and label this Average. You will
use the Function Wizard to calculate the Average value for each region. 3. Position the cursor in cell F4 and click on the Function Wizard button.
Select the Average function and click on OK. 4. Highlight the range B4:E4 in the Worksheet (if this range has not
already been added automatically).
Click on OK to complete. Check that the formula is correct and copy it
to the remaining rows in the column. 5. Save the file. Task 5 - The Sum Function 1. Still working with the summary.xls file, enter a formula using the
AutoSum button to calculate the Totals for each of the Quarters in the
Worksheet. Try doing this in two slightly different ways: . Highlight the range B4:B8. Note this range includes an empty cell.
Click on the AutoSum button. Look at the formula generated.
. Click in cell C8 and click on the AutoSum icon. Check the formula is
correct.
Note that the second method is quicker, but also more prone to error-
always check the formula. 2. Copy the formula in cell C8 to the remaining columns. Save the file. Task 6 - Count Functions 1. Working with the Summary.xls file again, use one of the COUNT functions
to count the number of cells in the summary Worksheet containing
numerical data. Place the answer in cell A11. 6. Use one of the COUNT functions to count the number of cells in the
summary Worksheet containing either numerical or text data (i.e. non-
blank cells). Place the answer in cell A12. 7. Use one of the COUNT functions to count the number of blank cells there
are in the range A1:E8 on the summary Worksheet. Place the answer in
cell A13. Task 7 - Precision as displayed 1. Enter the following numbers into cells A1:A4 in a new Worksheet: 5.5
5.5
5.5
5.5 2. Format the cells to display no decimal places. What happens to the
numbers displayed? 3. Enter a formula in cell A5 to calculate the average of the values in
cells A1:A4. 4. Examine the result of this calculation in cell A5. How many decimal
places are displayed? 5. Now set the format of cell A5 to display two decimal places. What
happens to the result? 6. Finally, set the precision of the entire Worksheet to display Precision
as Displayed using the Options menu. 7. Save the file as number.xls and close it. Task 8 - Absolute Referencing In this exercise you will use a spreadsheet (florist.xls) designed to
record sales of fresh flower arrangements. Formulae are required to
calculate the total value of each sale (the sum of the Price and the
Delivery Charge - this is a constant value) and the number of days which
have elapsed since the last Invoice Date (this is also a constant value in
the Worksheet). 1. Open the file florist.xls file in the IntroFormulaeFunctions folder 8. Enter a formula in cell D5 to calculate the Total Cost i.e. Price +
Delivery Charge. What sort of Reference should you use to refer to the
Delivery Charge? 9. Copy the formula to the remaining cells in the column. 10. Enter a formula in cell E5 (under the heading Days Outstanding) to
calculate the number of days since the Invoice Date (D1) i.e. Invoice
Date - Date of Sale. 11. Copy the formula to the remaining cells in the column. 12. Enter the labels Average, Minimum and Maximum in cells B22, B23 and B24
respectively. 13. Working with the Price column and using the appropriate functions,
calculate the average, minimum and maximum prices, placing the results
in cells C22, C23 and C24 respectively. 14. Save the file.
Task 9 - Using Names In this exercise you will use the florist-names.xls spreadsheet which is
based on the florist.xls file you used previously. This time you will use
named cells and ranges of cells to perform the same calculations. 1. Open the florist-names.xls file. 15. Give the cell containing the Invoice Date(D1) the name InvoiceDate. 16. Give the cell containing the Delivery Charge (D2) the name
DeliveryCharge. 17. Give the range of cells containing the purchase date (A5:A20) the name
PurchaseDate. 18. Give the range of cells containing the price (C5:C20) the name Price. 19. Enter a formula in cell D5 to calculate the Total Cost i.e. Price +
DeliveryCharge using Names to refer to the cells. 20. Copy the formula to the remaining cells in the column. 21. Enter a formula in cell E5 (under the heading Days Outstanding) to
calculate the number of days since the Invoice Date (D1) i.e. using
Names to refer to the cells. 22. Copy the formula to the remaining cells in the column. 23. Open the florist.xls file from the previous exercise and check that the
results you have obtained in the two exercises are the same.
Save the florist-names.xls file. Task 10 - COUNTIF & SUMIF
1. Open the file pivot.xls file. 24. Using the Create Name function, create names for all of the columns in
the spreadsheet (you should be able to do this in one simple
operation). 25. Create the following text in the respective cells:
Females I7
Males I8
Count J6
Total Salary K6
Average Salary L6
26. Use the COUNTIF function to find the numbers of females and males. The
formulae should be entered into cells J7 and J8 respectively. (Use
names in the formula instead of ranges). 27. In cells K7 and K8 use the SUMIF function to calculate the total
salaries for females and males respectively. (Use names in the formula
instead of ranges). 28. In cell L7 and L8 calculate the average salaries for females and males
using the values calculated in questions 4 and 5. 29. In cell I10 enter the label "High Earners" 30. In cell J10 enter a function to calculate the number of people earning
£50,000 or more Task 11 - Logical IF 1. Open the club.xls file. 31. Use the logical IF function to enter "yes"