Basic MS Excel 2003 Operations for 
Windows 2000 & XP Operating System
Computing Services Information Document
PC/Windows 2000-XP
    What is a Formula
Formulas calculate a list of numbers (or values) in a specific order.
A formula in Microsoft Excel always begins with an equal sign (=). The equal sign tells Excel that the following numbers or cells make up a formula. The general rules of mathmatics apply here in deciding the order of operation withinn a formula. 

Excel calculates the formula from left to right, according to a specific order for each operator in the formula. Formulas in Microsoft Excel follow a specific syntax (or order), that includes an equal sign (=) followed by the elements to be calculated (the operands), which are separated by calculation operators (+, -, *, /...etc). 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. You can change the order of operations by using parentheses. For example:

=(B5+50)/SUM(B2:D2)

The parentheses around the first part of the formula force Excel to calculate B5+50 first and then divide the result by the sum of the values in cells B2, C2, and D2. 
(Note: D2 is a cell location not an actual number. Excel will extract that number from the cell location and input it into the formula.)

Entering a Formula
TO
DO
Entering a Formula in a Cell 1. Select the cell in which you want to enter the formula.
2. Type an equal sign = to activate the Formula Window and type the formula if you already know it. In the figure above, a formula for the sum of Field1 and Field2 is created as Field3.
3. After this formula is typed in and <ENTER> is pressed, the result is immediately shown in Field3.
Entering a Formula through the Formula Bar 1. Click on Formula, then Paste Function to get a listing of all the Formulas available to Excel. 
2. Choose a formula listed and click on the OK button. Fill in the required fields within the parentheses in the formula.
3. After you have completed the formula or chosen one from the Formula menu, press <ENTER>		 Example is below this table.
Enter a Formula 1. Click the cell in which you want to enter the formula.Type < = >(an equal sign).
2. If you click Edit Formula   or Paste Function ,  Excel inserts an equal sign for you.
3. Enter the formula.
4. Press ENTER.

Example of Formula Bar: 

Basic Excel Functions  
TO
DO
Using AutoCalculate
 


 
 
 

To calculate the sum of a range of cells. 
Excel displays the sum of the range in the status bar after they have been highlighted (the horizontal area in Excel below the worksheet window). If the status bar is not displayed, click Status Bar on the View menu.

AutoCalculate can also perform other types of calculations for you. When you right-click the status bar, a shortcut menu appears. You can find the average of or the minimum or maximum value in the selected range. If you click Count Nums, AutoCalculate counts the cells that contain numbers. If you click Count, AutoCalculate counts the number of filled cells. Whenever you start Excel, AutoCalculate resets to the SUM function.

To Create a Total You can insert a sum for a range of cells automatically by using AutoSum. 
1. Click AutoSum  and select the cell where you want to insert the sum and, Excel's default formula is sum.
2. Press ENTER.
To change the suggested formula.
1. Click AutoSum
2. Select the range you want to total 
3. Press ENTER.
Fill in a series from a formula 1. Select the first cell in the range you want to fill, and enter the starting formula for the series.
2. Select the cell that contains the starting formula.
3. Drag the fill handle over the range you want to fill. The formula will be entered into each cell in the range that you have selected.

 

TO
DO
About Using Excel Functions to Calculate Values
 
 
Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure. For example, the SUM function adds values or ranges of cells.

Entering a formula

1. Click the cell in which you want to enter the formula.
2. Type = (an equal sign). If you click Edit Formula   or Paste Formula, Microsoft Excel inserts an equal sign for you. Enter the formula.
3. Enter the numbers (constant) to be calculated or their cell locations and operands. Note: Even though your formula begins with =sum, you can still create a formula using; +, -, *, /,...etc

4. Press ENTER

You can copy the same formulas into a range

1. Click the cell in which you entered your formula, and click Copy on the main menu bar.  
2. Hightlight a range of cells where you wish the same formula, and click Paste.

Note: This works well when you are copying cells that are in the same row or column, but when choosing scattered cell locations this technique does not work unless you create constants.

 

TO
DO
Using the Formula Palette to enter and edit formulas
 
 		 When you create a formula that contains a function (ex: sum, average, log, power, ....etc.), the Formula Palette helps you enter worksheet functions. As you enter a function into the formula, the Formula Palette displays the name of the function, each of its arguments, a description of the function and each argument, the current result of the function, and the current result of the entire formula.

To display the Formula Palette,
1. click Edit Formula  in the formula bar.

Enter a formula that contains a function 1. Click the cell in which you want to enter the formula.
2. To start the formula with the function, click Edit Formula  in the formula bar.
3. Click the down arrow next to the Functions box .
4. Click the function you want to add to the formula. If the function does not appear in the list, click More Functions for a list of additional functions.
5. Enter the arguments.
6. When you complete the formula, press ENTER.

Example: IF(SUM(K10:K19)>=5000, 10%, 5%)

You can also do this by clicking the Paste Function button and choosing the first fuction from the list, and click OK. The box below will appear and will prompt you for the next value.
Edit Functions in Formulas 1. Select a cell that contains a formula, and then click Edit Formula  to display the Formula Palette. 
2. The first function in the formula and each of its arguments appear in the palette. Edit the first function or edit another function in the same formula by clicking in the formula bar anywhere within the function.

 


 
Nested Functions When function reside within another function they are know as Nested Functions. Below is another example of a function that you may encounter.


 

 

DLM - (AS) 
Rutgers University, Camden 
S50-4150 
Revised 03-29-05
to the top of the page