Formulas are the engines of spreadsheets; they actually do the work.
|
If they are incorrect, the interpretation of the results will also be incorrect.
|
A formula can consist of several parts; all formulas must begin with an equal sign (=).
|
You can follow the equal sign with
ò cell references, such as A1
|
ò functions, such as SUM
|
ò parentheses
|
ò other components
|
Excel 97 gives you various ways of entering formulas into cells.
|
The most common way of entering a formula is to type it directly into the cell.
|
For example, to find the total for cells B3 through B5, beginners may type ^R=B3+B4+B5^r.
|
Although this works, it is not very efficient and increases the chances of data entry errors.
|
As you type, the characters appear in the cell as well as in the Formula bar.
|
The Formula bar is to the right of the Name box just under the bold, italic, and underline formatting buttons.
|
You can use all the normal editing keys - delete, backspace, direction keys, and so on - when entering a formula.
|
Another way to sum the values in a group of cells is to use a function.
|
To type the same formula =B3+B4+B5, you type ^R=SUM(B3:B5)^r.
|
In this case SUM is the function.
|
The range of cells to be summed (B3 through B5) appears, as always, in parentheses.
|
The cell addresses are separated by a colon which indicates a range.
|
However, you can use a comma in addition to the colon.
|
Commas indicate individual cell addresses while colons indicate contiguous groups of cells.
|
You don't have to worry about upper and lower cases when you enter a formula.
|
As soon as you press ^UEnter^u or move to another cell, Excel converts all letters to uppercase.
|
In addition to typing formulas and functions, you can enter cell addresses by combining keyboard entry with point-and-click.
|
After entering an operator, Excel allows you to point at the cells used in the formula and click to select them.
|
To add cells C1 to C9 as well as D1 and D2, you begin the keyboard entry by typing ^R=SUM(^r.
|
You then click cell ^UC1^u while holding down the ^UShift^u key, and then you click ^UC9^u.
|
Excel automatically places the cell addresses in the formula.
|
When you have finished entering the formula and have pressed ^UEnter^u, Excel recognizes that you have completed the formula and inserts the closing parenthesis for you.
|
Excel automatically calculates the sum.
|
If you don't type the first parenthesis before clicking the first cell in the formula, you get an error message in the formula cell.
If you don't type the last parenthesis in a formula, Excel enters it for you.
|
Operator precedence is the set of rules that Excel uses to perform its calculations in a formula.
|
In simple arithmetic formulas, Excel will calculate in the following order:
ò multiplication (*) and division (/)
are treated equally
|
ò division (/) before addition (+)
|
ò addition (+) and subtraction (-)
are treated equally
|
You can override operator precedence by using parentheses in your formulas.
|
If an addition or subtraction operation is enclosed in parentheses, it will be calculated before division or multiplication.
@@
@@
The AutoSum feature allows you to total a group of cells to the left of the active cell or above it.
|
When you click the ^UAutoSum^u button, Excel looks for numbers above or to the left of the current cell and highlights them with a moving dotted line known as a ^Rmarquee^r.
|
You press ^UEnter^u to total the cells.
|
If the correct cells are already highlighted, simply double-click ^UAutoSum^u to show the total.
|
When there are cells both to the left and above the active cell when you click ^UAutoSum^u, the column cells take precedence over the rows and are highlighted.
|
^UAutoSum^u automatically totals the cells in column A.
|
You would have to drag the mouse along the row to sum the row cells instead of the column cells.
|
If you have a table surrounded by blank cells, AutoSum can total all of its columns and rows.
|
To select the entire table, click any cell in the table.
|
You then press ^UCtrl+*^u and the entire table is selected.
|
You click ^UAutoSum^u to total all the columns and rows.
|
The AutoCalculate feature is handy if you need to do calculations on numbers you have highlighted, but don't want to include a formula as part of your table.
|
When you right-click the running total in the Status bar, a menu is displayed for further calculations.
|
The default for AutoCalculate is the SUM function.
|
The ^UAutoCalculate^u menu allows you to
ò calculate the average of the selected cells
|
ò count the number of cells highlighted
|
ò count the number of highlighted cells that
contain numbers or formulas
|
ò display the maximum or minimum value
of the selected cells
|
ò sum the selected cells
|
You choose an option from the ^UAutoCalculate^u pop-up menu, such as ^UCount^u.
|
Now when you hold down ^UShift^u and drag the mouse, the number of rows or columns is displayed in the Status bar.
|
Note that the option must be chosen while a cell from within the table is selected.
If the active cell is anywhere else on the worksheet, the new option will not apply to the table.
@@
An absolute cell reference or address is one that doesn't adjust when you copy or move a formula.
|
An absolute reference includes the $ symbol for both the column letter and the row number, such as $C$4.
|
You use absolute cell references when you refer to fixed address cells.
|
Examples of absolute references would be key variables, such as the inflation rate or a standard discount.
|
A relative cell reference in a formula is one that adjusts when copied from one cell or range of cells to another location.
|
Once you have entered the formula, you move the pointer to the fill handle in the bottom right-hand corner of the cell and drag the outline down to the last cell to which you want to apply the formula.
|
For example, you can copy a cross-total formula from one cell to other cells below it, and the formula will automatically apply to the relevant row for each cell.
|
The moment you release the mouse button, the cells fill with the results of the copied formula.
|
The process of entering a formula and copying it by dragging the fill handle is known as ^Rfilling^r ^Ra^r ^Rformula^r.
|
Excel also allows mixed references in which only one part of the address is absolute.
|
The following are all the possible types of cell references:
ò A1 - relative reference
|
ò $A$1 - absolute reference
|
ò $A1 - mixed reference in which
the column is absolute
|
ò A$1 - mixed reference in which
the row is absolute
|
You can examine the formulas without having to scroll through the worksheet.
|
This allows you to see the formulas rather than the results.
|
To view the formulas, you select the ^UTools^u ^U- Options^u menu item.
|
You click the ^UView^u tab in the Options dialog box.
|
Then you select the box entitled ^UFormulas^u.
|
The result is a display of the formulas rather than the results.
@@
@@
If you plan to use dates in formulas, make sure that when entering a date, Excel is able to recognize it as a date, otherwise your formulas will produce incorrect results.
|
You do this by checking that Excel has right-aligned the entry in the cell, and by viewing the entry as displayed in the formula bar.
|
Excel 97 recognizes entries as dates and can use these dates in formulas.
|
For example, if Jun 95 is entered in one cell, Jul 95 in the next, and a formula entered in a number formatted cell to subtract the first from the second, the correct result (30) is calculated and displayed.
|
You can enter dates in cells using month text with spaces, such as Jun 1 1995. They are then displayed with a hyphen in the cell and as mm/dd/yy format in the formula bar.
|
Such cells can be used in formulas, but the key in many cases is to have the cell containing the formula formatted as a number, otherwise Excel tries to display the result as a date.
|
You can create a formula to add a date cell to a number to give a date result. By default, Excel will show the result in the format of the first date cell and will simply show Jun-1 1995.
|
You can reformat the formula result cell to dd/mm/yy format and the result will display correctly as 6/1/95.
|
Microsoft Excel stores dates as sequential numbers known as serial values and stores times as decimal fractions, because time is considered a portion of a day.
|
Dates and times are values and therefore can be added, subtracted, and included in other calculations.
|
For example, to determine the difference between two dates, you can subtract one date from the other and get the result in days.
|
You can view a date or time as a serial number or a decimal fraction by changing the format of the cell that contains the date or time to General format.
|
The best way to deal with times is to enter the time into a cell in a recognized format.
|
Some examples are
ò 11:30:00 am for 11:30 am
|
ò 11:30:00 AM for 11:30 am
|
ò 11:30 for 11:30 am
|
ò 11:30 pm for 11:30:00 pm
|
An example of an unrecognized format would be 1130.
|
You can also combine dates and times, as in 6/1/95 11:30 for 11:30 a.m. on June 1, 1995.
|
Excel 97 supports two date systems: the 1900 and 1904 date systems.
|
The default date system for Excel 97 is the 1900 system.
|
You can change to the ^U1904 date system^u using the ^UTools - Options^u menu item.
Click the ^UCalculation^u tab, and then select the 1904 date system check box.
|
The 1900 date system uses January 1, 1900 as its start date.
|
Each date has a serial number based on how many days have passed since that date.
|
So January 1, 1900 has the serial value 1 and December 31, 1999 has the serial value 36525.
|
When you enter a date in Excel 97 and you enter only two digits for the year, Excel sees 99 as 1999, 20 as 2020, and so on.
|
Excel presumes a century of 20 for all years up to December 31 2029, and a century of 19 for all years over and including 30.
|
For example, if you type ^R"5/28/19"^r, Excel assumes the date is May 28, 2019.
|
Another example is ^R"5/28/91"^r. Excel assumes the date is May 28, 1991.
|
Microsoft Excel calculates the underlying, or stored, values in cells.
|
The value you see on the screen depends on how you choose to format and display the stored value.
|
For example, a cell that displays a date as 6/22/96 also contains a serial number that is the stored value for the date in the cell.
|
You can change the display of the date to another format (for example, to 22-Jun-96), but changing the display of a value on a worksheet does not change the stored value.
|
When you enter a date formula, such as ^R="6/1/92"-"5/1/92"^r Excel interprets the text as a date in the mm/dd/yy format.
|
When you enter date formulas that don't refer to cell coordinates, you must remember to enclose the dates in quotation marks.
|
Excel then converts the dates to serial numbers and calculates the difference between them, in this case 31 days.
|
To accommodate leap years when doing calculations, you enter 365.25 rather than simply 365.
|
Some types of worksheets may benefit from an outline.
|
An outline lets you display hierarchical information at different levels of detail.
|
To do this you would choose ^UData - Group and Outline -^u ^UAuto Outline^u.
|
For more information on outlining, see CBT Systems course ^RAdvanced Excel 97^r.
