01110000 01110101 01110010 01110000 01101100 01100101



Intro to Spreadsheets with MS Excel

As the use of word processing is a technological advance over the use of typewriting, it’s also true that the use of spreadsheets is an important technological advance over the use of calculator. Among the advantages of a spreadsheet compared with a calculator:

• A spreadsheet creates a document, with much more power than most calculators can.

• A spreadsheet can produce graphs/charts, with much more power than most graphing calculators can.

• A spreadsheet can easily recalculate when the data of a calculation is changed, with much more power than most calculators can. In the 1970s, this, along with word processing and computer games, was one of the most important stimulants to popularization of personal computers.

You can start Excel with a new data file (“workbook”) by clicking Start, All Programs, Microsoft Office, Microsoft Excel 2010. Typically, a new workbook has 3 worksheets. A worksheet has lettered columns and numbered rows. The intersection of a column and row is a cell. A cell may be named or addressed in notation of the form

columnLetter rowNumber

E.g., the cell in column J and row 3 is cell J3. Note the “Name box” over columns A and B, which displays the name of the cell currently containing the cursor.

Formatting issues include the size of a cell. You can make a column wider or narrower as follows.

• Place the cursor in the row in which columns are labeled, at the right edge of the column whose size is to be changed.

• Drag-and-drop the right edge of the column, rightwards to make the column wider, or leftwards to make the column narrower. Alternately, if you double-click on this edge, the width of the column is adjusted to the width of the widest entry in the column.

A similar procedure allows you to alter the height of a column. You might wish to do this to allow data to “wrap around” and appear to occupy multiple lines. Once sufficient height is given to a row, a cell in this row can have its data wrapped as follows. With the cursor at the cell

• On the Home tab, click Format.

• Click Format Cells from the resulting menu.

• On the Format Cells dialogbox, on the Alignment tab, check the textbox labeled Wrap Text and click OK.

Note you may see several of the “#” character in a cell that is not wide enough to show its value.

The value displayed in a cell is determined by the spreadsheet designer in any of several ways, including:

• Enter a text (“string”) constant, such as a name, explanatory phrase, etc. Such values often appear as row or column headers.

• Enter a numeric constant, such as 7 or 87,526.92

• Enter a formula. Even if the value in a cell is calculated so easily that it’s easier to calculate in your head and enter the “answer” rather than entering a formula, it’s usually wise to enter the definition of the display value by formula. Often, a calculation must be repeated due to changes in the data values on which the calculation depends. If the cell is defined by a formula, the spreadsheet can automatically recalculate when a data value is changed.

You might use the fill-color button on the home tab to fill the background of cells with a non-standard color, perhaps as a way of calling attention to the cell(s) so formatted.

A formula starts with an equal sign. Many formulas are based on arithmetic calculations using the following operators:

|Operator |Meaning |Example |

|+ |Addition; also, as a unary operator |A5 + A6 |

| | |+8 |

|- |Subtraction; also as a unary operator |B2-C2 |

| | |-4 |

|* |Multiplication |B5 * 10 |

|/ |Division |A5/A7 |

|^ (“caret”) |Raise to power |J5 ^ 2 |

A formula may also use functions that are part of the Excel software. A function is used in the format

functionName(parameterList)

where a parameter list is a list of parameters or arguments (representations of data to be operated upon by the function). A parameter list may be empty (i.e., there are no parameters) for certain functions; if there are 2 or more parameters, they’re separated in the list by commas.

Among the commonly used functions:

• SUM(parameterList) – the number of parameters is arbitrary. This function adds the values of the parameters.

A parameter may be a cell, a constant, a cell range (to be explained below), or a more complex expression. Examples:

o SUM(B5, B7, B9*10) is equivalent to B5+B7+B9*10 - here, the 2nd form might be preferred because it’s shorter.

o A cell range is a rectangle of cells denoted in the form

topLeftCorner:lowerRightCorner

E.g., H8:J9 is the rectangle of cells running from column H to column J and from row 8 to row 9. For example, the expression SUM(B5:J5) is equivalent to B5+C5+D5+E5+F5+G5+H5+I5+J5 – here, the first expression should be preferred, as the shorter one.

Note the button on the Home tab labeled ∑. If, during editing of a formula, you click this button, the SUM function enters the formula, with a cell range as a proposed parameter list. If the proposed range is incorrect, you can edit it.

• The maximum value among numeric values can be computed via the MAX function, in the form

MAX(parameterList)

where the number of parameters in the parameter list is arbitrary. E.g., to compute the maximum numeric value among the cells in the range B2:B10, we can use the formula =MAX(B2:B10)

• Note the menu of commonly used functions offered by the drop-down button adjacent to the ∑ button. Using this menu is another shortcut.

• The average value of the numeric values in a parameter list can be found using the function

AVERAGE(parameterList)

where the number of parameters is arbitrary (but, often, is a cell range). E.g., the formula =AVERAGE(B2:B10) computes the average value of those entries in B2:B10 that are numeric.

• The minimum value among numeric values can be computed via the MIN function, in the form

MIN(parameterList)

where the number of parameters in the parameter list is arbitrary. E.g., to compute the minimum numeric value among the cells in the range B2:B10, we can use the formula =MIN(B2:B10)

• IF(logicalExpression, expressionIfTrue, expressionIfFalse)

where we use the first parameter to decide whether the 2nd or 3rd parameter should be used as the value of the function. The parameters:

o “logicalExpression” is an expression that evaluates as TRUE or FALSE.

o “expressionIfTrue” is the expression that is used as the value of the IF function if the logicalExpression is TRUE.

o “expressionIfFalse” is the expression that is used as the value of the IF function if the logicalExpression is FALSE.

Logical expressions are often based on comparisons. Excel’s comparison operators:

|Operator |Explanation |Example |

|> |Is greater than |B5 > C5 |

|< |Is less than |G6 < 0 |

|= |Is equal to |H8 = K8 |

|>= |Is greater than or equal to |N8 >= 10 * Q8 |

|B$18,B$19,B$20), 2) if we want to round to the nearest cent, or

=ROUND(D2*IF(D2>B$18,B$19,B$20), 0) if we want to round to the nearest dollar.

An amortization schedule allows you to study how a loan is repaid. Among the data that can be calculated from an amortization schedule:

• Totals of interest payments. This is important because, to the lender, interest is gross profit, and to the debtor, interest payments are sometimes tax deductible.

• How much does the debtor owe at a given time during the duration of loan payments? This is important because, e.g., if interest rates go down, the debtor wants to refinance the loan at the new, lower interest rate. What the debtor owes at this time becomes the principal of a new loan at the new, lower, interest rate.

Many loans are repaid as “ordinary annuities” – all payments are of the same size; interest is compounded at the end of a payment period; a payment is due at the end (last day) of a payment period. A variation with a simpler formula for payment size: all payments except the last are of the same size; the last payment is a “balloon payment” for whatever is necessary to pay off the loan. In this variation, we can assume that, e.g., if there are 60 payments, then each payment (except the last) is about 1/60-th of the original principal.

We can use the spreadsheet’s ability to do rapid calculations in order to solve problems in variety of ways. For example, we can realize certain goals by using the spreadsheet as an experimental tool, experimenting with the values of the independent variables until we obtain a valid solution to the problem we’re working on.

Imagine yourself as charged with the responsibility of determining pay raises for a small staff of employees, subject to the following constraints (e.g., that might be the result of negotiations):

• The total of the raises must be at least 2.75% of the current year’s total salaries for these employees.

• The total of the raises must be at most $250 over 2.75% of the current year’s total salaries for these employees.

• Employees are rated from 1 (bad) to 5 (excellent). The higher the rating, the larger the percentage increase for the employee.

To have Excel calculate whether the current data shows a valid solution: If the total of (proposed) raises is in D10, the minimum acceptable in H13, and maximum acceptable is in H14, then a valid solution is characterized by

H13 < D10 < H14.

To understand how this is stated in an Excel formula, recall that a chain of inequalities is an abbreviation. The chain above abbreviates

H13 < D10 and D10 < H14.

The “and” operator, in Excel, is a function. Thus, an appropriate formula:

=AND(H13 ................
................

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

Google Online Preview   Download