Overview of Spreadsheets

1. Introduction

   Aim of This Document
   Who is This Document For?

2. Spreadsheet Fundamentals

   What is a Spreadsheet?
   The Structure of a Spreadsheet
   The Content of a Spreadsheet
   The Basic Operation of a Spreadsheet
   Functions
   Graphs
   Presentations and Formats
   The Spreadsheet as a Database
   Macro Languages

3. Spreadsheet Editing

4. Spreadsheet Design

   General Rules
   Block Design
   Linear Design
   Multisheet Design

5. The Future of Spreadsheets

6. Available Software

   Microsoft Works
   Quattro Pro
   Excel

7. Appendix 1 Functions Common to All Spreadsheet Packages
8. Appendix 2 Charts Common to All Spreadsheet Packages
9. Appendix 3 Extra Functions and Graphs in As-Easy-As
10. Appendix 4 Excel Functions and Graphs

Introduction

Spreadsheets are still the most popular PC software in the world. In 1977 the first personal computer, the Apple II, was produced. One year later the first spreadsheet, Visicalc, was produced and became a run away success. People bought the Apple II in order to use Visicalc. In 1981 IBM produced their first PC, and with it came Lotus 1-2-3. Lotus quickly overtook Visicalc in sales and went on to become the most popular PC program ever. The early success of the Personal Computer has been largely attributed to its use with spreadsheets.

Aim of This Document

There are many different commercial spreadsheets on the market today. Some of their functions and facilities can vary widely, especially in their ability to produce graphical output. However, the basics of any spreadsheet are the same, so the principles of using a spreadsheet are the same no matter what package you happen to have chosen. This document aims to give the user an overview of what spreadsheets in general can do so that the user can decide how to use them for their own particular task. It is not intended to provide the user with a comprehensive manual for any one package.

This document will also give an overview of the three main spreadsheet packages available at Leeds so that the user can select the one most suitable for their needs and relative computer experience.

Who is This Document For?

This document is for anyone who:

is wanting to know what a spreadsheet is in order to decide whether they need to use one or not.

has been told to use a spreadsheet and wants basic information on the abilities of spreadsheets and ideas for spreadsheet design.

needs to use a spreadsheet supplied by Computing Service and wants to know what is available and which spreadsheet to choose.

is wanting to buy a spreadsheet for themselves and wants to know what features to look out for.

Spreadsheet Fundamentals

What is a Spreadsheet?

Figure 1 Spreadsheet Fundamentals.

This is the basics of it. It is from this simple structure some very complex and powerful calculations may be performed.

At the intersection of any column with any row you get a cell. A cell is like a box that has a label and contains information. The label of the cell is called the cell reference or cell address and this is derived from the cell's column letter and the row number. So, a cell in column C and row 3 has the cell reference C3.

The Structure of a Spreadsheet

For the computer a spreadsheet contains two layers. The storage layer and the display layer. The storage layer contains the actual information in the spreadsheet as it is stored by the computer in the spreadsheet file. The display layer is what you actually see on screen.

The Storage Layer

1. Numbers

2. Text

3. Formulae

Each type of information is identified by the starting letter: a number starts with a number; a piece of text starts with a letter or some other character; a formula starts with either a number or a special symbol, typically '+', '=' or '@'.

The Display Layer

The Content of a Spreadsheet

Numbers

As mentioned above, any number you type in to a spreadsheet will be stored in the same format, no matter what it refers to. So the result of a scientific calculation will be stored in the same way as a figure relating to a budget proposal. This is fine as far as calculations are concerned, but it does not make the spreadsheet easy to read. Therefore most spreadsheets have the ability to display numbers in different ways. Typically there are fixed formats (normal numbers with a fixed number of decimal places), scientific notation, currency and commas (to delimit 1,000's). All these formats are for display purposes only, but they are important in making your spreadsheet clear to yourself and other readers.

Text

Text in a spreadsheet is used mainly for labelling and annotating the numbers so that you know what the numbers refer to. Apart from being displayed alongside the numbers all text in a spreadsheet is ignored by the program. However, most spreadsheets have some simple database capabilities including searching and sorting, and in this instance the text becomes quite important. The text is always important when it comes to presenting your numbers and calculations to other people. Most spreadsheets therefore allow you to present your text in a variety of typefaces, sizes and styles to add visual impact to a presentation.

Formulae

A formula can also be made up of references to other cells. This means that we can set up a system of cascading calculations where the result from one cell can be fed into the formula of another. A typical formula can look something like this:

+C3+C4

What this means is "Add the contents of cell C3 to the contents of cell C4 and display the result in the current cell". A formula involving cell references has to start with a special formula symbol to tell the spreadsheet that this is a formula rather than something else. This is necessary because you want to allow for the occasions when a column heading might also be a cell reference. For example, if you were entering in data about different British roads you may want a column heading saying 'A1'. The spreadsheet needs to know that this A1 is a label (referring to the A1 road between London and Edinburgh) and not a reference to the cell A1.

The Basic Operation of a Spreadsheet

The spreadsheet has a number of advantages over other methods of performing numerical calculations. Let's take the simple example of a shopping list, as shown in Figure 2.

Figure 2 A Spreadsheet Used With A Shopping List.

Some advantages are immediately obvious. Firstly, all the numbers are visible and can be checked for errors and corrected - unlike a calculator where the number disappears as soon as you perform the calculation. Secondly you can label the numbers with some text to identify what the numbers relate to.

The calculation is set up the with the result appearing at the bottom of the column. This in itself is not much of an advance on any other calculating mechanism. However, if you change one of the numbers in the calculation say the price of sausages then as you press the key to enter that number into the spreadsheet you will find that the calculation is performed again automatically and the new total arrives. This is illustrated in Figure 3.

Figure 3 Recalculating Totals.

This is probably the biggest reason why spreadsheets are so useful. You set up the calculation to happen automatically and then you can play around with the figures to see what results you get. This is the basis of what is termed What-If Analysis or Sensitivity Analysis that underlies a lot of prediction and modelling work.

Functions

The calculations that a spreadsheet can perform are further enhanced by a large range of functions. These functions often work on a number of cells together to produce a single figure result. A typical example is the SUM function that adds up all the cells in the range you specify.

For example, we used the formula +C3+C4+C5+C6+C7+C8 to add up the column of figures in our shopping list. Instead of writing that formula in we could have written the function SUM(C3:C8) and this would have produced the same result whilst being a lot quicker to write.

The precise functions you have available to you will depend on what spreadsheet you are using (see the Appendices for the lists of functions available in each spreadsheet program). The syntax with which they are used will also vary from spreadsheet to spreadsheet. However, the list of functions in Appendix 1 should be basic to every spreadsheet program.

These functions are the power tools for successful spreadsheet work. Using functions it is possible to build up very complex calculations step by step. In advanced spreadsheet programs you will find the basic set of functions augmented by functions to use the spreadsheet as a simple database, improved statistical functions that allow you to do regression, t-tests and analysis of variance, Fourier Analysis and some engineering calculations.

Graphs

It is often quoted, "A picture speaks a thousand words" and this becomes very true when you are confronted with a large table of numbers. Although you know these numbers mean something, their physical quantity makes it difficult to pick out any patterns you may want to identify. It is for this reason that graphing facilities are now considered essential. They are able to provide an instant visual overview of your data - they help spot any differences in a set of values - or a trend that one or more sets may be indicating. A spreadsheet can have all the number crunching power of a supercomputer, such as University' Computationally Intensive Facility, but if you cannot interpret meanings from the numbers it is not a lot of use.

Every modern spreadsheet, almost without exception, will have some ability to graph the numbers you enter. The range of graph types a spreadsheet can produce varies from package to package, as does quality. However, they all ought, at least, to draw bar-charts, pie charts, line graphs and scatter grams. A list of the more common graph types, with examples of their application is given in Appendix 2.

For example in 1976 a survey of Academic Staff grades related to sex was performed. The summary table of result is as follows:

Figure 4 University Academic Staff in the UK by Grade (1976).

From the raw figures it is clear that in each category there are more male than female employees. However, what is not clear is whether this percentage is maintained over the grades, or whether it changes as the grades get higher. If we plot the results on a 100% stacked column chart then the pattern becomes clearer, as shown below.

Figure 5 Graphic Representation Of The Staff Grade Table.

The graph shows us that there is a much higher percentage of female employees in the lower grades than in the upper grades. Precisely why this should be so is perhaps a matter for debate.

Presentations and Formats

The spreadsheet's role in processing data and drawing graphs has meant it has developed into a presentation tool as well as an analysis one. Some of the more powerful spreadsheets even have the ability to create slide shows for data presentation. Every spreadsheet should have some drawing and formatting ability to make the data it contains more presentable either on screen or in print. Such drawing abilities may include the ability to draw lines on the spreadsheet to separate titles from data, or to point an arrow at particular figures for emphasis. The formatting abilities should include the ability to put different cells of data in different fonts and the ability to italicise or embolden text for emphasis. Even simple features such as these can make a big difference to the readability of a spreadsheet when you come to present your data to someone seeing it for the first time.

The Spreadsheet as a Database

Although spreadsheets are designed to work mainly with numbers, in real life you are not just working with numbers but textual data as well. This has lead to the development in many spreadsheets of the ability to act as a simple 2 dimensional, or flat-file, database. Although they are not as easy to use as a specialised database package they are often adequate for simple tasks. More importantly, the database functions that the spreadsheets supply can be very useful in searching and sorting data in a spreadsheet.

For example, say you have a collection of data from a survey looking into the differences in salaries of male and female employees in a company. By placing all your data in a spreadsheet you can sort the data according to sex (M or F) and then according to salary, you could calculate the percentage of employees of each sex at each grade of employment. You could extract records from your data calculate the average salaries of male and female employees for each grade. From these summary statistics you could then go on and set up a t-test or analysis of variance to check whether any differences you have picked up are statistically significant.

Macro Languages

It very soon became apparent to those using spreadsheets that it would be useful to have a method of automating common, repetitive tasks. So spreadsheets were developed with the ability to record a set of actions or commands and play them back at a single keystroke. This ability was then developed to provide functions for altering the display, performing file management and then on to do a variety of complicated programming and interface tasks. Nowadays spreadsheet macro languages are powerful application development tools and are starting to replace traditional 3GL languages for many tasks.

The rival development of different spreadsheets by different companies has unfortunately meant that no two macro languages are even remotely similar. This means that you cannot transfer a macro written in one spreadsheet to another one and expect it to work. However, with the development of more sophisticated 4GL languages, the increasing use of spreadsheets in the production of Computer Based Learning materials and the advent of Object Oriented programming techniques, many spreadsheet macro languages are beginning to represent BASIC and so are starting to converge a little more.

Spreadsheet Editing

With any computer program it is important to be able to edit the information easily. Spreadsheets must allow you to change the numbers in a cell, alter a formula or edit a piece of text - i.e. alter the content in the storage layer. You need also to be able to alter the display format of any of the cells - i.e. alter the display layer.

Editing of a spreadsheet is done on discrete units of the spreadsheet known as ranges. A range is defined as any rectangular block of cells. The smallest range is a single cell, the largest the whole spreadsheet. The important point is that the block must be rectangular, so for example you cannot have an 'L' shaped range.

Just as you can move text and paragraphs around in wordprocessors you can move cells around in spreadsheets. This is especially useful if having typed in all your numbers you then discover that you haven't left any room for titles and need to move the whole block down the spreadsheet a little. The task is usually accomplished by selecting the range of cells you want to move and then performing the Move command. An alternative to using the move command might also be to insert blank rows or columns at the top of your spreadsheet to create the necessary space.

Spreadsheet editing abilities cover more than just altering what is already in the spreadsheet. In many cases there are editing functions that speed up the entry of information or the setting up of formulae in a worksheet. For example, say we are setting up a series of calculations for the sales of a product over three months. The example spreadsheet is shown below.

Figure 6 Editing A Spreadsheet.

To total up all the months we might have to write the following formulae in column F:

F2 @SUM(B2.E2)

F3 @SUM(B3.E3)

F4 @SUM(B4.E4)

To save us typing these formulae individually every spreadsheet has a copycell or autofill procedure. The way this works is that you enter in the formula into one cell, say F2. You then copy that cell to the other two below it. Now you would expect the other two cells to read exactly the same as F2, but instead the spreadsheet will recognise the formula and translate the cell references B2 and E2 to become B3.E3 or B4.E4, depending which row number the formula is in. Thus instead of writing out nearly identical formulae into each cell you just write it out once and copy it. The spreadsheet deals with the cell references relatively and alters them accordingly.

What about the situation where you want a cell reference to stay the same. Say you wanted to add VAT to all the figures in the sales spreadsheet. It would make sense to put the value of VAT into just one cell and reference it. Then when VAT changes we only have to change the value in one cell, not in every cell containing a VAT calculation. Say we place VAT in cell G1, and set up a formula to calculate Totals + VAT in the column H. The first formula in H2 would read +F2*G1. If we copied it down then the next formula would read +F3*G2 because it would be copied relatively. In order to 'fix' a cell reference and make sure it isn't altered we have to make it an absolute address. In most spreadsheets this is done by placing $ signs in front of the column and row reference.

Therefore the formula in cell H2 will now read +F2*$G$1. When copied down the column the reference G1 (represented by $G$1) will be kept.

Some spreadsheets can also fill out a series of numbers or dates, so that if you place the value 1 in a cell and use the autofill command to copy it across a range of cells, then the spreadsheet will automatically create the series 1,2,3,4,.. in the subsequent cells. In more powerful spreadsheets you can have more complex series where you enter the first two or three cells and the spreadsheet automaticallly calculates the rest of the series. Some spreadsheets will even fill in the days of the week, months of the year or quarters if you give the starting word (e.g. Mon, Jan, Quarter 1).

Naming Ranges

The system of referencing cells by their row and column is a very efficient one but in practice can make using a spreadsheet that some else has set up quite difficult. It would be far easier to work with sensible English names rather than obscure codes. For example, the following two formulae may mean the same, but which one makes most sense to you?

=D17*$G$1

=Cost*VAT

To achieve this readability, most spreadsheets allow you to name a cell or a range of cells. You can then use this name instead of the cell reference in formulae. So, in the above example where the cell G1 contains the VAT rate (currently 17.5%) we can either refer to the 17.5% using the cell reference, G1, or we can name the cell 'VAT' and use that. It means that people reading our calculations, or us reading them back later, can understand what is going on immediately without having to actually look at cell E7 to see what it contains.

It is also possible to name whole ranges. If you have a table of expenditure where one column contains all the individual costs for, say, books, you can name that column range 'Books'. Then at any point on the spreadsheet you can use that name instead of the range it refers to. Again, consider which of the following makes more immediate sense:

=SUM(W67:W99)

=SUM(Books)

By a good use of naming ranges and individual cells you can create a spreadsheet that reads a lot more like English, and a little less like Assember.

Spreadsheet Design

There are a few fundamental rules to spreadsheet design which if not obeyed will present, at best, a difficult spreadsheet to use, and at worst a spreadsheet producing erroneous results. One USA company once lost $2 million because of an incorrect spreadsheet. Therefore every spreadsheet you produce should have the characteristics listed below.

Accuracy       If the numbers in your spreadsheet, or the calculations
               you set up, are simply wrong then the results and your
               conclusions will be wrong.  This is a simple case of
               Garbage In = Garbage Out.
Clarity        As mentioned above, if you need to present your
               spreadsheet to someone else, then they must be able to
               see quickly what the numbers are and how the results
               have been achieved.  Also if you need to edit your
               numbers yourself, you need to be able to see quickly
               and easily what the numbers relate to and what you need
               to change.
Flexibility    Will you want to change the spreadsheet design at any
               time?  Is it being set up to solve one problem or are
               you developing it as you work at the problem?  Are you
               setting the spreadsheet up to solve a sequence of
               similar problems?  A spreadsheet that is flexible so
               that it can be easily changed is well worth having.
Efficiency     Some spreadsheets can be laid out to minimise the time
               it takes to number crunch the calculations.  If you
               have a very large spreadsheet then this may be worth
               while looking at.  However, it is probably more worth
               while to look at how easy the spreadsheet it to use for
               yourself.  Can you find the areas you want to work on
               easily?  Is entering the data a fairly simple process?
               Maximising the efficiency of the spreadsheet operator
               is important if the spreadsheet is to be used a lot.
Auditability   The results that the spreadsheet produces are only as
               good as the numbers and the calculations that produce
               them.  Is it easy to prove to someone else that you
               have got your calculations right in the spreadsheet.
               If an error did occur would you be able to locate it
               quickly.  When you design your spreadsheet make sure
               that you either know, or can easily find out, what is
               going on in the spreadsheet at any stage.

The idea of INPUT - PROCESSING - OUTPUT which underlies the "Garbage In = Garbage Out" principle is a good basis for starting your design. You therefore need an area of your spreadsheet that deals with the input, an area that deals with the calculations and an area that deals with the resulting output. On top of this you will want an area to identify what the spreadsheet is, an area to document what the spreadsheet does, and finally an area for any macros you may have written for the spreadsheet.

Every spreadsheet design should have up to six separate areas for the different information. These areas are:

Identification Area

Documentation Area

Input Area

Work Area

Output Area

Macros Area

General Rules

First a few general rules about spreadsheet design. As a spreadsheet normally looks the same no matter what area of it you are looking at it is important that you know where all your data is and how you can navigate your way to it easily. For this it is useful to sketch out your design on a piece of A4 graph paper. Divide the paper up into sections, label each section as to what it contains and give the cell references that delimit the areas on the spreadsheet. It is also helpful to have a table of contents for your spreadsheet what it contains and the range of cells that each item is held in as this is useful for auditing your spreadsheet later.

Navigating around a spreadsheet can be done either by the row/column-full, or else in pages (screenfuls). The pages method is often the most convenient and the quickest so it makes sense to organise your data on the screen so that comprehensible chunks can be viewed on a single page, or a screen, at a time. This may mean leaving odd columns or rows blank in order to group the data properly, but this is worth it for everyday use

Due to the unexciting nature of spreadsheet interfaces, in reality there are only really three major design ideas that you can employ, namely block design, linear design and multisheet design.

Block Design

This sort of design is ideal for small spreadsheets, and is also very popular with novice spreadsheet users. Everything is positioned as near to the top left hand corner as possible, as shown below.

Figure 7 A Representation Of A Block Design.

The advantage is that just about everything is visible on the one screen. The disadvantage is that this design is not at all flexible, and you would have to do some awkward moving around with areas to expand the identification, documentation or work area.

Linear Design

The linear design is overall much better as it allows for expansion of any of the areas.

Figure 8 Representations Of Linear Design.

Which of the two directions you choose is up to you. The vertical design is far more like you would lay out the problem on a word processor. The horizontal design means that you can format whole columns to the right width for the information they contain as that will be the same the whole way down the spreadsheet.

Multisheet Design

Many spreadsheets allow you to make links to other spreadsheets, or else incorporate several spreadsheets into one file. The speed of these links varies from geting an instantaneous result to a quite significant delay. If speed is not a problem then it can be useful to put different items on different spreadsheets and have one spreadsheet as the summary data. The conceptual model might look like this:

Figure 9 Multiple Spreadsheet Work.

As a general rule, multiple spreadsheets are easier and quicker to work with than one huge spreadsheet, so if you can use a number of small spreadsheets then this is advised.

Your spreadsheet design need not be limited to the models set out above. In many cases the job that the spreadsheet is doing has a standard layout that you are expected to use. However the principle of blocking your spreadsheet into different areas is still a good one, and it is especially important to have all the identification material in the top left hand corner so that the user of the spreadsheet knows what they are looking at immediately the spreadsheet opens.

The Future of Spreadsheets

Lotus have always been at the forefront of spreadsheet technology and most of the major advances in spreadsheets occurred first with Lotus 1-2-3. It was not surprising therefore that Lotus should be the company to produce the first three dimensional spreadsheet. What this is, in effect, is several spreadsheets all in one big file. The main advantages are in more elegant spreadsheet design, especially for bigger problems. Having 3-D spreadsheets also allowed a set of spreadsheets to be created and edited together in one operation rather than having to edit them all individually. Finally it allows for calculations to occur across spreadsheets in exactly the same way as they occur within spreadsheets. So for example, as well as being able to say "Add up all the numbers in Row 5" or "Give me the average of all the numbers in Column F", you can now say "Give me the sum of all the numbers in C5 across all the spreadsheets". When you have a collection of identical spreadsheets where C5 always contains the vital figure, this adds an extra level of power and flexibility.

Many leading spreadsheets now incorporate this 3-D idea. Lotus has the original 3-D sheet, Quattro Pro for Windows has a 'notebook' and Excel has a 'Workbook' where several spreadsheets, charts and macro sheets can be bound in one general file.

Lotus have not stopped at 3-D sheets, however. Consider the problem where you have, say, 4 sales reps covering 3 regions and you want to look at their performances over the 4 quarters of the year. Now with traditional spreadsheets you can either look at a table of reps against regions for a single quarter, or of reps against quarters for a single region, or of quarters against regions for a single rep. In other words you can only see one 'slice' of the cube of data easily. Lotus have tackled this problem with a new product called Improv. The idea behind Improv is that you can enter all the information into a worksheet (which does not operate the same way as a traditional spreadsheet) and then simply view the data any way you like by positioning the categories of information against each other. In this way Improv is able to handle up to 12 dimensions of data in a flexible manner. Another innovation with Improv is that it does away with the A1 referencing system. All columns and rows must be named according to what they are, so all individual cells have proper names rather than obscure references.

The advantage of all this is that it is very good for viewing many sorts of multidimensional data. It is not, however, as flexible as a traditional spreadsheet for laying out tables, performing various types of analysis or creating simulations. It is likely therefore that Improv will complement spreadsheets rather than replace them. The impact of Improv has already been felt on the spreadsheet market place and it is thought that all the main Windows spreadsheets manufacturers will develop 'Data Modeller' type programs that will take data from their spreadsheets and enable the user to view it in a variety of ways.

Available Software

Microsoft Works

Works is an integrated package that combines a spreadsheet with a database and a word processor. The database part of Works is not much more powerful than the database facilities found in many standalone spreadsheets, but it is less convenient as it requires more effort to place data from the Works database into the spreadsheet.

Ease of Learning

Ease of Use

Functions

Graphics

Miscellaneous

Conclusion

If you are new to computers and you need to use a word processor and a spreadsheet fairly quickly then Works is for you. Even if you decide to move onto a more powerful package later the data you have typed into Works will not be lost and can be transferred to the more powerful package without retyping. The spreadsheet familiarity and the use of the mouse will be skills that you will find useful in other packages. The excellent help system and the intuitive interface also make it possible to use this program for a single, once only, operation, so long as the user has some basic experience in computers already.

People who need to use a spreadsheet for big calculations will quickly find the limits of Works. Users who need engineering or financial functions may find themselves having to set these functions up themselves when other spreadsheet packages have them pre-programmed. Added to this, Works macro language is not very powerful at all. Similarly people who want more powerful, or even just more impressive, graphics will find the scope of Works limited. It is possible to export Works data to use with another package, for example Cricket Graph, but using two packages where one will do is undesirable. If you are not in a hurry to produce results and analyses then it is worth looking properly a more powerful spreadsheet and learning to use that instead.

Excel

Ease of Learning

Ease of Use

Functions

Graphs

Miscellaneous

Conclusion

Appendix 1 Functions Common to All Spreadsheet Packages

ABS(x)              Gives positive value of x.
ACOS(x)             Gives arccosine of x.
ASIN(x)             Gives arcsine of x.
ATAN(x)             Gives arctangent of x.
ATAN2(x,y)          Gives arctangent of an angle defined by cooordinates x and
                    y.
AVG(x, y ... )      Gives the average of the values in the range references x,
                    y ..
CHOOSE(a,x,y,..)    Uses variable a to select an option from the list x, y, ...
COLS(x)             Gives the number of columns in the range of reference x
COS(x)              Gives the cosine of angle x measured in radians
COUNT(x, y, .. )    Gives the number of cells in the range references x, y, ..
CTERM(x, y, z)      Gives the number of compounding periods needed for an
                    investment earning a fixed rate x to grow from a present
                    value, y to a future value z.
DATE(x,y,z)         Gives a date number for the date specified by x=year,
                    y=month and z=day.
DDB(w, x, y, z)     Uses the double declining balance method to find the
                    amount of depreciation in a specific period z. w is the
                    amount you paid for the asset x is the amount you expect
                    to obtain when you sell the asset y is the number of time
                    periods you expect the asset to be in use.
EXP(x)              Gives e to the power x, or else the Anti-log of a number
                    to base e.
FV(x, y, z)         Gives the future value of a ordinary annuity of equal
                    payments x earning a fixed interest per term y over the
                    number of terms z.
HLOOKUP(x, y, z)    The result is the value in the cell referenced. x  is the
                    value at the head of the column you want to lookup y  is
                    the range reference that the table is contained in. z  is
                    the number of rows down from the head of the column
VLOOKUP(x, y, z)    Gives a value from a table. x  is the value in the left
                    most column that defines the row to be referenced y  is
                    the range of the table being searched z is the number of
                    columns across that the function moves to pick up the
                    value The function returns the value inside the cell
                    referenced.
IF(x, y, z)         Returns y if x is true.  Returns z if x is false.
INDEX(x, y, z)      x  is the range of the table being looked up y  is the
                    column number (start numbering from 0) z  is the row
                    number (start numbering from 0)
IRR(x, y)           Gives the internal rate of return for the cash flow series
                    in range reference y.  The value x tells IRR where to
                    begin the iterative mathematics.

ISERR(x)            If x is the error condition this function returns the
                    value True.
FN(x)               Gives the natural logarithm of x.
LOG(x)              Gives the base 10 logarithm of x.
MAX(x, y, z, ...)   Gives the maximum value in all the ranges x, y, z.
MIN(x, y, z, ... )  Gives the minimum value in all the ranges x, y, z, ...
MOD(x, y)           Gives the modulus of x/y.
NOW()               Gives the current date and time number for the current
                    date and time.
NPV(x, y)           Gives the net present value of a series of cash flow
                    payments represented by numbers in the range y, discounted
                    at a fixed rate x per period.
PI()                The mathematical constant PI.
PMT(x, y, z)        Gives the periodic payment for a loan for an investment of
                    principal x based on a fixed interest y per compounding
                    over a given term y.
PV(x, y, z)         Gives the present value of an ordinary annuity of equal
                    payments x, earning a fixed rate of interest y over
                    several periods z.
RAND()              Generates a random number between 0 and 1.
RATE(x, y, z)       Gives the rate of interest per compounding period needed
                    for an investment y to grow to x over several compounding
                    periods z.
ROUND(x, y)         Rounds the number x off to y decimal places.
ROWS(x)             Gives the number of rows in reference x.
SIN(x)              Gives the sine of the angle x measured in radians.
SLN(x, y, z)        Uses the straight line depreciation method to find the
                    amount of depreciation in one period. x is the amount you
                    paid for the asset. y  is the amount you expect back when
                    you sell the asset. z  is the number of periods in years
                    you expect to use the asset.
SQRT(x)             Gives the square root of the value x.
STD(x, y, z, ... )  Gives the standard deviation from all the values in the
                    ranges x, y, z, ...
SUM(x, y, z, ... )  Gives the total of all values in the ranges x, y, z, ...
SYD(w, x, y, z)     Uses the sum of digits method to find the amount of
                    depreciation in a specific period. w  is what you paid for
                    the asset. x  is what you expect to get back from the
                    asset. y  is the number of periods you expect the asset to
                    be in use. z  is the period for which you want to find the
                    depreciation.
TAN(x)              Gives the tangent of the angle x measured in radians.
TERM(x, y, z)       Gives the number of compounding periods necessary for a
                    series of equal payments x, earning a fixed interest y per
                    period to grow to future value z.
VAR(x, y, z, ... )  Gives the population variance of the numbers in the ranges
                    x, y, z, ...

Appendix 2 Charts Common to All Spreadsheet Packages

Bar Chart: This shows the numerical differences between various categories of data. Each row or column of the spreadsheet appears as a set of bars with the same pattern or colour.

Stacked Bar Chart: Here the bars are stacked on top of each other to give you a cumulative total for each category as well as the differences between the series.

100% Bar Chart: The bars are all stretched to be the same length in order to show what percentage each bar is of the total.

Line Chart: This type of graph is good for showing trends or changes in data. Each line represents a row or column from your spreadsheet of data.

Area Chart: This is a line chart where the lines are 'stacked' to show the total in each category. The change in the total can then be viewed. Here the graph is the same data as above but the lines are cumulatively stacked. Thus the value for any one point is the distance between itself and the point directly below.

Hi-Lo Close Chart: This type of graph shows the range between high and low values within one category. It is useful to show error bars on charts of data, or to show the spread of data around a line of best fit.

Pie Chart: This type of chart is best if you want to show the percentage breakdown. Each pie slice represents an individual value; the whole pie represents one spreadsheet column or row.

X-Y Chart or Scattergram: This type of graph shows numerical values from the spreadsheet along both X-axis and Y-axis. Each marker represents the relationship between the X and Y values.

Appendix 4 Excel Functions and Graphs

Functions

Excel has the following range of functions:

42 engineering functions

50 financial functions

19 date and time functions

52 mathematical functions

71 statistical functions

18 reference functions

16 database functions

22 text and string functions

Total of 316 functions (including ones not listed).

Excel has a number of what are called 'Add-in' functions. These are really an associated macro library of complex calculations for specific tasks. The total list of possible add-in functions is given below:

Anova: Single Factor

Anova: Two-Factor With or Without Replication

Correlation

Covariance

Descriptive Statistics

Exponential Smoothing

F-Test: Two Sample for Variance

Fourier Analysis

Histogram

Moving Average

Random Number Generation

Rank and Percentile

Regression

Sampling

t-Test: Paired Two-Sample for Means

t-Test: Two-Sample Assuming Equal Variances

t-Test: Two-Sample Assuming Unequal Variances

z-Test: Two-Sample for Means

Graphs

Below are some examples of the sort of graphs one can produce with Excel.

3D Column Graph

3D-Pie

Contour Graph

Enhanced Graphs

About This Document

This document is copyright University of Leeds. Permission to use material in this document has been granted by the Information Officer in the Computing Service.