Shylock Homes Estate Agents

Year 9 Excel functions spreadsheet task

Excel 2007 Version

Shylock Homes Estate Agents

Year 9 Excel functions spreadsheet task

You will learn about

  • Data validation
  • Using spreadsheet functions

=SUM()

=COUNT()

=AVERAGE()

=MAX()

=MIN()

=LARGE(,)

=IF(,,)

  • Displaying spreadsheets in formula view
  • Creating charts using data from non contiguous ranges

Excel uses functions to help make statistical, financial or other calculations. A function is a pre-set formula which contains two parts, the function name and its arguments. The arguments are the conditions, cell references or values upon which the function works. In this exercise you will use =SUM(), =COUNT() , =AVERAGE(), =MAX(), =MIN(), =LARGE(,) and =IF(,,).

Shylock Homes is a chain of estate agents with four regional offices in Easterby, Northlea, Southton and Westlink.This exercise uses the values of properties sold in their sales offices over the period of a week.

Using what you already know about entering text and data, widening columns etc, enter the details shown in the table below. Save the file as SHYLOCK.XLS

Alternatively you may use the template provided.

A / B / C / D / E
1 / TOWN / EASTERBY / NORTHLEA / SOUTHTON / WESTLINK
2 / £250,000 / £375,000 / £248,000 / £260,000
3 / £325,000 / £240,000 / £262,000 / £245,000
4 / £263,000 / £265,000 / £273,000 / £450,000
5 / £242,000 / £282,000 / £320,000
6 / £285,000 / £285,000
7 / £305,000
8
9 / TOTAL VALUE
10 / NUMBER SOLD
11 / AVERAGE PRICE
12 / MOST EXPENSIVE
13 / LEAST EXPENSIVE

Setting the currency formatting to zero decimal places

Whenever you set currency formatting, Excel automatically uses two decimal points by default. To change his proceed as follows:

  • Select the range of cells B2:E8
  • Right click and choose format…
  • Apply the formatting shown in the diagram

You can also use the Currency and Decrease Decimal buttons on the toolbar to format the numbers as currency to zero decimal places.

Setting data validation

Data validation is where you set rules on certain cells so that they will only accept a certain type or datain a certain ranges. In this example we will set the cells which contain prices of houses so that you get a warning if the price is too high or too low.

Start by selecting the range of cells B2:E8

Go to the Dataribbon and select Validation…

Set the validation so that the cells selected should only accept a value between 100,000 and 1,000,000.

Click the Error Alert tab and enter the alert messages as shown. Make sure that you change the style to warning.

You should get the following error message if you enter a value greater than 1,000,000 or less than 100,000

Finding the total value for each area office

In cell B9 you need a function containing the TOTAL VALUE for Easterby sales. The formula needed is=SUM(B2:B8)A blank cell is left in case you want to add a new sale.

There are different ways to do this: either by typing the formula manually or by using the AutoSum feature. The quickest is to use the AutoSum Tool, , on the toolbar.

  • Select cell B9 and click on the AutoSum button.
  • Check that the correct cells are highlighted and click  or press enter.
  • Replicate the formula across cells C9:E9 using the Fill handle.

Finding the number of sales for each area office

In cell B10 you need a count of sales. The formula needed is =COUNT(B2:B8)

  • Again the quickest way to enter this is to select cell B10 and then choose count from the AutoSum drop down list
  • Check that the correct cells are highlighted and click  or press enter
  • Replicate the formula across cells C10:E10 using the Fill handle.

Finding the average value of sales for each area office

In cell B11 you need the average sales. The formula needed is =AVERAGE(B2:B8)

  • Again the quickest way to enter this is to select cell B11 and then choose average from the AutoSum drop down list
  • Check that the correct cells are highlighted and click  or press enter
  • Replicate the formula across cells C11:E11 using the Fill handle.

Finding the maximum and minimum value of sales for each area office

  • Use the Max and Min functions to find the maximum and minimum values of sales in B12 and B13
  • Replicate the formulae across cells C12:E12 and C13:E13 using the Fill handle.

Creating a pie chart showing the town names and Total Value Figues

  • Start by highlighting the data that you want to use, select the cells B1 – to E1
  • Because the other data you need is in a non contiguous (non touching) range you will have to hold down the Ctrl key while you select the second range B9 – E9
  • Once you have highlighted the data that you need, go to the insert ribbon and create a Pie Chart using that data

Adding another office

Shylock Homes Estate Agents acquires another office in Midchester. This office has already made some sales and these are to be added into the spreadsheet.

  • Select the SOUTHTON column.
  • Right click and select Insert in order to insert a Column between NORTHLEA and SOUTHTON
  • Widen the column as necessary
  • Type the heading MIDCHESTER.
  • Add house sales for £350,000, £255,000, £285,000, £240,000, £320,000, £295,500

Look at the pie chart and check that the new town has its own slice and an entry in the legend.

Preview your work

Go to Print Preview, Setup

Add your name and date and tutor group to the footer

Additional formulas – LARGE and IF

Finding the area with the greatest value of sales

The managers of Shylock Homes Estate Agents want to find the sales office that has the highest value of sales. You can do this using the LARGE function

  • In cell A14 type ‘HIGHEST SALES AREA’.
  • In cell B14 type =LARGE(B9:F9,1) This will find the highest value in the range B10 to F10. (Note that there is a 1 after the range separated by a comma. This is the ARGUMENT which tells the formula to find the highest value in the range – a 2 would show the second highest and so on).

Finding the area who qualify for a bonus

The managers of Shylock Homes Estate Agents want to show the sales office with the highest value of sales who will qualify for a bonus payment. You can do this using the IF function.

  • In cell B15 type =IF(B9=$B14,“bonus”,” “) This will check if the total value for that office matches the highest value shown in cell B15. If the value does match – it will put in the second argument “bonus”, if the value doesn’t match it will enter the nothing shown in “” marks in the third argument.
  • Replicate this formula across all the cells B15 to F15.
  • The dollar signs in the formula will allow absolute cell referencing.

When you have finished this exercise your spreadsheet showing the values and numbers of properties sold in your five offices for the week should look like this:

Showing the formulas that you have used

Make a copy of the spreadsheet (this is done by clicking on the tab at the bottom of the sheet and dragging sideways whilst pressing the ctrl key).

Click the formula ribbon and select show formulas

Your formula view should look like the diagram above

Printing off your work

  • Change the tab name of your first sheet to “Shylock Homes”.
  • Change the tab name of the second sheet to “formulas”
  • Working in Print preview – setup, set the Header to read as the tab names ‘Shylock Homes” and “formulas”
  • Ensure that your name, tutor group and date are in the Footer of both sheets
  • Save your work and print both sheets

Teachnet 2009 Page 1 of 6