Excel for Surveyors

Philip Bowcock, Natalie Bayfield

• Publisher: Estates Gazette
• Year: 2014
• ISBN:
  9781135321376
• Edition: 1
• Topic: Jura
• Subtopic: Eigentumsrecht

This book is an introduction to Microsoft Excel™ concentrating on the program's unique application to the work of surveyors. Useful operations such as the creation of valuation tables and automation of conventional valuations are explained with the aid of step by step examples and screen-shots. The setting up of discounted cash flow problems and development appraisals are given special attention, and specific problems posed by over-rented property and leaseholds are also considered, additionally the book includes examples of database and chart functions useful to management and agency surveyors

Chapter 1

Your computer

There can be very few offices today which do not have among their equipment a modern computer. It is well known that developments are moving so fast that today’s “state of the art” machine will be considered obsolete in about three year’s time, but for the purposes of this volume the facilities offered by machines currently available should be adequate for the foreseeable future.

This is not intended to be an introductory textbook on how to use a computer. However, for those who have a machine but maybe have never switched it on, or having done so are at a loss to know what to do next, we give a short explanation.

The object of the first three chapters is to explain the use of some of these facilities to practising surveyors and valuers, many of whom were at work before modern computers were thought of. It is not easy to come to terms with machinery which is internally very complicated, but which schoolchildren are using every day.

Today there are two main types of desktop computer (which term also includes laptop computers). The most common one is the PC (personal computer) using the system developed by IBM and now incorporated into Microsoft Windows®. The less common one is the Apple Macintosh (generally agreed to be easier to use). Microsoft Excel will run on either system with no significant difference. Files are also interchangeable between the two – if you are using a Macintosh just copy files to a PC-formatted floppy disk and put it in the drive of the PC.

There are two principles to be considered when acquiring computer hardware and software – broadly you get what you pay for, and there will be something bigger, better and cheaper available tomorrow! Over the last 15 years the prices of computers have consistently declined in real terms regardless of inflation and the facilities which they provide. If you are currently considering purchasing a computer for use with Excel then any of the current IBM variety or Apple Macintosh machines are almost certain to have adequate memory and file space.² However if you intend to store graphics, such as photographs taken with digital cameras, with your data then you may need substantially more memory and disk space. Presentation software such as Powerpoint, which is part of the Microsoft® Office suite, may also require more memory

There are numerous pieces of information in a computer specification, but the two most significant are the “memory” or “RAM” and the “disk” or “filestore”. “Memory” is the working part of the computer where all the information is held and the calculations are done while you are actually using it. It can be thought of as your desk, on which you can work on your document. Internally it consists of many millions of tiny electronic switches which are organised to hold the data that you are working on and translate it into a form in which you can view it on your monitor. As soon as the machine is switched off all contents of the memory disappear for ever (like sweeping all the paper on your desk into the bin).

“File space” by contrast may be thought of as a bookshelf on which you can put away your work when you have finished. In the computer this is primarily the “hard disk” which consists of one or more rotating metal disks with a magnetic surface in a sealed unit on which data can be stored in a similar manner to the familiar tape recorder. The difference is that the recording head is not in contact with the disk but is very close indeed to the surface – a distance much less than the diameter of the human hair. Consequently hard disks are fragile and must never be opened except in a “clean” workshop.

The “unit of measurement” for both these spaces is the “byte” which consists of eight on-off electronic switches known as “bits”. A set of eight bits has 256 possible on-off combinations, each of which can represent one numeric or alphabetic character, a punctuation mark, or other symbol, or a special code for the computer. These are known as the ASCII codes (American Standard Code for Information Interchange).

You should expect to have at least 32 megabytes (million bytes) of memory and 2 gigabytes (thousand million bytes) of disk space.

The monitor is the means by which the computer communicates with you, and the most common size for desktop monitors is 15 inches. The next size, 17 inches costs a little more but can be well worth it by enabling you to see more of the worksheet. Portable computers are of course limited by the size of the case, but most can be linked to an external monitor if desired. Do not forget to observe the general advice on posture when using the computer for long periods. The Health and Safety (Display Screen Equipment) Regulations 1992 relate to use of computers in places of employment but should be used as guidelines all situations.

Every computer requires an operating system to make it work. This is loaded up at startup and produces the pictures on the screen, maintains the file system and many other functions. Current versions (Windows 98 and Macintosh 8.0 upwards) operate as we shall describe. If you use earlier versions you may find slight differences in operating Excel.

It is impossible to discuss every possibility in considering software as complicated and versatile as Excel. Having described the principal operations likely to be of use to surveyors, we suggest that you work through each example we have described in order to become familiar with it, but then try out the other options for yourself.

It is hoped that this text will enlighten those who are still somewhat diffident; suggest new possibilities to those who have used some of the basic operations, and generally remove some of the mystique which still discourages many surveyors.

Finally, you should remember that using Excel efficiently, like using many other complicated tools, requires practice and regular use. Don’t expect to remember everything immediately, and be prepared to revise your knowledge from time to time.

²  By “current” we mean any PC Pentium or Macintosh Power PC machine.

Chapter 2

Introduction to Excel

2.1 Description

Microsoft Excel is the most widely used worksheet for desktop computers and has been around since version 1 was marketed in 1985. The very first worksheet program, VisiCalc³ (1978), was followed by another, Microsoft Multiplan, in 1983. These pioneers were remarkable for their facilities in their time but by comparison with the current version they were very limited in the operations they could perform. Today, Excel provides such a large variety of resources that it is most unlikely that anyone will use every single facility.

There are other worksheets around, notably Lotus 1–2–3 and Clarisworks, but many of the features are similar, and you should be able to carry out many of the operations discussed later with these other applications.

This chapter is designed to take you through all the common features of Excel, the commands and language that will enable you to move around Excel, and some of its little tricks, so that you don’t have to refer to other resources. However there are no large examples since this is just a tour. Some of the details may seem tedious if you have used Excel before. If it still seems complicated after you have finished this chapter, don’t worry. Once you put it into practice on valuations in later chapters everything here will become second nature.

If you are accustomed to using word processing software, the first thing you will notice when you open Excel is that your monitor shows not a blank page, but a grid of cells. Word processing software, such as Word, assumes you want to fill your screen with text whereas Excel is based on an array of rows and columns. (If your screen is blank when you load Excel take your mouse-operated arrow to the word File at the top left corner of the screen, click once with the left mouse button⁴ and then click again on the word New.)

2.2 The mouse

You will notice there is a marker which is called the “cursor” on the screen and as you move your mouse around on the desk the cursor moves around on the screen in the same direction. You will also see that as it moves it sometimes changes shape – around the sides of the screen it is an arrow, in the fourth line down it is an I bar, and when over any part of the grid it becomes a thick white cross. This is the selection cross.

“Clicking” means hovering the cursor over a cell or another part of the screen which you want to select and pressing the left mouse button once unless stated otherwise, eg click twice or click with the right mouse button. If you click a cell its border becomes black and the cell is then “selected”.

“Drag” means select a cell, hold down the mouse button, and move the pointer to another cell. The first and last cells and all those in the rectangle formed will be selected – try it. This is very useful if you want to carry out an operation on more than one cell, for example to clear all data or change the appearance of the cells.

Above the cells you will see a horizontal grey strip with the column headers labelled A, B, C, etc. To select an entire column, click on the column header. The entire row or column will appear black except for the top cell. Try this.

Similarly, in the vertical grey strip to the left of the cells you will see the row headers labelled numerically 1, 2, 3, etc. Rows can be selected in the same way.

2.3 Cell references

Each cell in the worksheet has an address or cell reference rather like an Ordnance Survey sheet or map. Each cell is in both one column and one row at the same time. The letter at the top of that column and number at the beginning of that row together define the cell’s address or reference. For example when the black border is in the first column on your grid and the first row, you have selected cell A1. Excel also displays the cell reference in the name box towards the top left corner of the screen. Select a cell and see if you can find its reference displayed in the name box.

Worksheets can be huge. Each worksheet in Excel 97/98 can have 65,536 rows and 256 columns. Within this, the limitation is the total amount of memory available in your machine. Quite what one would do with such a large worksheet is difficult to imagine, but no doubt somebody somewhere has tried. In practice it is usually much better to separate parts of a very large project into several different worksheets within the same workbook.

2.4 Some conventions

Throughout this volume we shall use the following:

Return	means press the Return key.
Control	means hold down the Control key while you press the indicated character key. The ALT key, and on the Macintosh the Command and Option keys are used similarly

Following common practice, words indicating a keystroke are printed in this style of text.

2.5 Worksheets and workbooks

Excel assumes that you want to fill your screen with numbers, and the grid provides these handy little cells in which to put them. This is because each individual number in your worksheet is a separate item. Most importantly, by separating numbers in this way we give them their own unique home and address on the worksheet, which is important when we come to writing formulae. Excel, incidentally, refers to its grids as “Worksheets”, but elsewhere you will hear them referred to as “Spreadsheets”.

The grid which first appears on your monitor is one worksheet in a workbook whic...