CAT / FIA FMA Syllabus B. Data Analysis And Statistical Techniques - Computer spreadsheet system - Notes ^{1 / 2}

Role and features:

Most of the numerical forecasting and budgeting techniques mentioned in the previous chapters will be carried out far more efficiently with the help of computer software packages.  Packages have specific statistical applications (index numbers, time series analysis, regression) but they can also be of a more general nature (e.g. spreadsheets and databases).

What is a spreadsheet?

A spreadsheet is a computer package which is divided into rows and columns. The intersection of a row and a column is known as a cell.

Cell contents

The contents of any cell can be one of the following

• Text. A text cell usually contains words, descriptions, key words.

• Values. A value is a number that can be used in a calculation.

• Formulae. A formula refers to other cells in the spreadsheet, and performs some sort of computation with them.

Formula bar

The formula bar allows you to see and edit the contents of the active cell. The bar also shows the cell address of the active cell.

Examples of spreadsheet formulae

All Excel formulae start with the equals sign =, followed by the elements to be calculated (the operands) and the calculation operators. Each operand can be a value that does not change (a constant value), a cell or range reference, a label, a name, or a worksheet function.

Formulae can be used to perform a variety of calculations. Here are some examples

1. =C4*5. This formula multiplies the value in C4 by 5. The result will appear in the cell holding the formula.

2. =C4*B10. This multiplies the value in C4 by the value in B10.

3. =C4/E5. This divides the value in C4 by the value in E5.

4. =C4*B10-D1. This multiplies the value in C4 by that in B10 and then subtracts the value in D1 from the result. 

   Note that generally Excel will perform multiplication and division before addition or subtraction

5. =C4*117.5%. This adds 17.5% to the value in C4, for example in sales tax.

6. = (C4+C5+C6)/3. Note that the brackets mean Excel would perform the addition first.

Without the brackets

Excel would first divide the value in C6 by 3 and then add the result to the total of the values in C4 and C5.

• 2^2 gives you 2 to the power of 2, in other words 22. Likewise = 2^3 gives you 2 cubed and so on.

• = 4^ (1/2) gives you the square root of 4. Likewise 27^(1/3) gives you the cube root of 27 and so on.

Formulae with conditions

< less than 
= greater than or equal to
> greater than
 not equal to

The SUM button Σ

In Excel, the standard toolbar has a button Σ that simplifies adding a column or row of numbers. When you click the AutoSum button, Excel creates a sum function for the column of numbers directly above or the row of numbers to the left. Excel pastes the SUM( ) function and the range to sum into the formula bar.