Purple.niagara.edu



Intro to Spreadsheets with MS Excel

Many computer technologies developed as improvements on previous non-computer technologies. For example, word processing is so successful as an improvement on typewriting, that typewriting is pretty much obsolete. Similarly, spreadsheets, primarily used for arithmetic calculation, offer major advantages over non-computer technologies such as hand calculators and pencil and paper calculation.

One of the major advantages of the spreadsheet is that its formulas can recalculate immediately if any of the data upon which a formula depends, is changed. Indeed, probably the two most important spurs to the popularity of personal computers in the late 1970’s and early 1980’s were the power of word processing compared with typewriting, and the power of a spreadsheet, particularly to recalculate.

An Excel workbook may have several worksheets, indexed by their tabs. You may change the name of the sheet by clicking its tab and editing the name.

A worksheet has lettered columns and rows that are numbered. The intersection of a column and a row is a cell, whose address is named by the in the format

columnLabel rowLabel

For example, the cell in column G and row 5 has the address G5, as is confirmed in the “name box” when the cursor is in that cell.

Definitions of what cells display: a user may enter constant values, text, dates, Boolean constants (True or False), etc. A cell may also be defined by a formula. A formula typically starts with an equal sign. Many formulas are used to specify arithmetic calculations. Often we refer to other cells in formula, by using the cell’s address as a variable whose value is displayed in the cell.

Notice also that as we edit a formula, we can use “cursor pointing” clicking on a cell to place the cell’s address into the formula being edited. Often, this is faster and less error prone than typing the cell’s address.

Since many formulas are used for arithmetic you should know the arithmetic operators:

|Operator |Explanation |Example |

|+ |Addition |=B2+B3 |

|- |Subtraction; also, as unary |=B2-B3 |

| |operator |=-C8 |

|* |multiplication |=B2*B3 |

|/ |Division |=D9/3 |

|^ (caret) |Raise to power |=C12^2 |

Other elements used in formulas include functions. Excel provides hundreds of functions. Each is used in the format

FunctionName(parameterList)

where the parameter (or argument) list has 0 or more entries; if more than 1 parameter, then parameters are separated by commas. A parameter of a function may be a constant, a cell, a “range” of cells, or an expression. Among the commonly used functions:

• Sum – used to total several arguments. For example, the formula

=SUM(B2:B4)

says to add up the values of the cells in the range B2:B4. A range of cells is a contiguous rectangle of cells, specified in the format

upperLeftCorner:lowerRightCorner.

Other valid uses of the Sum function

=Sum(B2:F6) (note range of multiple columns and multiple rows)

=Sum(B5, B7, B12:D15, 25*F4, 10)

• The IF function is used to distinguish between cases. It uses the format

If(logicalCondition, expressionForTrue, expressionForFalse)

The “logicalCondition” is evaluated as either True or False. If it’s True, then the expressionForTrue is evaluated as yielding the value of the IF function; otherwise (the logicalCondition evaluates as False), then the expressionForFalse is evaluated as yielding the value of the IF function.

For example: Tax calculations typically take the form

Tax rate * taxable income

Suppose a state has a 2-tiered tax system, in which taxable income above a criterion level (say, $50,000) is taxed at one rate (say, 2%), and taxable income at or below the criterion level is taxed at a lower rate (say, 1.5%). Suppose, further, the taxable income is in cell D2. Then an expression for the tax rate is

If(D2 > 50000, 2%, 1.5%)

and the formula for the tax would be

= If(D2 > 50000, 2%, 1.5%) * D2

There are several ways in which the formula above can be improved. For example: The formula above could require 5 decimal places to show the exact product. However, US currency transactions usually use no more than 2 decimal places for fractions of a dollar. There is often a difference between the precision of a calculation and the precision of the display of a cell. The sum of unrounded numbers may appear to be slightly in error due to the accumulation of roundoffs. To avoid the appearance of such errors, we can use the Round function, whose form is

Round(expression, numberOfDecimalPlaces)

in order round off the value of the first parameter to the number of decimal places specified as the 2nd parameter. Thus, a better tax formula:

= Round(If(D2 > 50000, 2%, 1.5%) * D2, 2)

(the highlighted section corresponds to “expression,” above).

Another way to improve the tax calculation makes use of the observation that the “magic numbers” of the tax calculation ($50,000; 2%; 1.5%) could be changed by legislation. Suppose that, instead of the small number of clients we have in our example worksheet, we have 5,000 clients, each with their own row. If any of the parameters of the tax calculation is changed, then, if we compute as discussed above, each of the 5,000 cells would have to have its formula revised. Suppose, instead, we place these values in cells and refer to the cells in our tax calculation formulas. Then a change in tax legislation would call for revision of the 3 cells with these values, rather than 5,000 cells. Thus, if we place the criterion income in B13, the high rate in B14, and the low rate in B15, we might think that the formula

= ROUND(IF(D2 > B13, B14, B15) * D2, 2)

will work for us – but this formula won’t copy correctly down its column. For example, in the comparison, the reference to B13 is meant to refer to the criterion income, but this gets adjusted to B14 instead of remaining B13 when copied to the next row. What we need is a different kind of cell reference. The references used previously are relative references that adjust, as discussed above, through copy-and-paste operations. We can use a fixed (absolute) reference to fix (prevent adjustment of) the reference with respect to its column, its row, or both, during copy-and-paste operations. Thus, there are 4 ways to refer to B13:

• B13 - relative in column, relative in row

• $B13 - fixed in column, relative in row

• B$13 - relative in column, fixed in row

• $B$13 - fixed in column, fixed in row

Copying formulas: You can copy a formula, and what gets copied is the form of the formula, with its relative cell references adjusted according to the translation between the source of the copy and the destination of the paste. For example, when we copied the formula

=B2-C2

from D2 to D3, we found that the formula in D3 had been adjusted to

=B3-C3

corresponding to the fact that between the source (D2) and the destination (D3) is a translation of 0 columns (so the column references in the formula were unchanged) and of 1 row (so the row references in the formula were adjusted by 1 row).

However, fixed references stay fixed (unchanged) through copy-and-paste operations. For example, suppose the formula of cell F7 is copied to cell H11. Note the column translation is from column F to column H, which is 2 columns to the right; therefore, all relative column references are adjusted 2 columns to the right. Similarly, the row translation, from row 7 to row 11, is an increase of 11-7=4 rows, so relative row references are adjusted by an increase of 4. Everything else in the formula is copied without change.

|If F7 has the formula |then H11 will have the formula |

|=B4 + C14 |=D8 + E18 |

|=$B4 + C14 |=$B8 + E18 |

|=B$4 + C14 |=D$4 + E18 |

|=$B$4 +C14 |=$B$4 + E18 |

The first parameter of the IF function is a logical condition (evaluates as True or False). Typically, this condition is a comparison, such as the logical expression

D2 > B$13

The logical operators you might use to form comparisons are:

|Explanation |Operator |Example |

|Is greater than |> |D2 > B$13 |

|Is less than |< |H8 < I20 |

|Is equal to |= |K5 = 0 |

|Is greater than or equal to |>= |Q6 >= 2 * H5 |

|Is less than or equal to | ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download