| What is a Formula? | Order of precedence |
| How to enter a formula | How to use AutoSum |
| Mathematical operators | Example Formulas |
What is a Formula?
Three applicable definitions from Wordnet:
- a group of symbols that make a mathematical statement
- recipe: directions for making something
- a conventionalized statement expressing some fundamental principle
Formula Bar
The Formula bar is located below the toolbars and contains a Name Box and Formula bar. As you type your formula the text will appear in both the cell and the formula bar. |
 |
Name box displays the name of the active cell or selected cells.
Cancel - Cancels immediate changes made to the active cell.
Enter - Enters the number, text or formula into the active cell.
Functions key offers help on creating advanced functions or formulas.
Formula Bar is where you will enter and edit formulas
How to enter a formula
>>> All formulas in Excel must begin with = equal sign <<<
Formulas have three parts:
= |
which signifies that the cell needs to be calculated |
Function Name |
such as SUM or AVERAGE |
Argument |
on which the formula will operate. The argument contains cell references or numbers (or a mix of references and numbers) |
The argument must be enclosed by parentheses.
Entering a Formula in a Cell :
- click on the cell
- type the =
- enter the formula.
No = sign? Excel will treat the expression as text. It won't calculate!
Mathematical operators
Operators are just symbols that represent mathematical operations. The basic set of operators is:
| + Addition - Subtraction |
* Multiplication / Division |
^ Exponentiation |
Order of precedence
This is a lesson from your elementary school math class (one many of us have forgotten). When a formula contains several operators, there is a predetermined order in which they will be performed:
Order |
Operation | Symbol |
Comments |
1 |
Negation | - |
|
2 |
Percentage | % |
|
3 |
Exponentiation | ^ |
|
4 |
Division | / |
|
4 |
Multiplication | * |
|
5 |
Addition | + |
|
5 |
Subtraction | - |
*String Concatenation means (roughly) to join together data and interpret the information in a single message This operator can be used to join several text strings together into a single string. When a text string is used within a formula the characters must be enclosed in speech marks " ". You can use this operator to join any two values (text or numeric) in order to create a single text string. |
6 |
String concatenation | & |
|
7 |
Equal To | = |
|
7 |
Not Equal To | <> |
|
7 |
Less Than | < |
|
7 |
Greater Than | > |
|
7 |
Less Than or Equal To | <= |
|
7 |
Greater Than or Equal To | >= |
Please Excuse My Dear Aunt Sally is the catch phrase to remember.
Like the minus sign used to negate values, % takes precedence over the standard operators.
Why worry? Ignoring the Order of Precedence can give you very incorrect results.
Examples - These equations have the same operators, but lack the ( ) to establish order of precedence
Formula
Result Formula
Result =10+8/2
14 =1-4^2
-15 =(10+8)/2
9 =(5-4)/2
0.5 =5+-4^2
21 =(5+-4)^2
1
Using Parentheses
Excel uses parentheses as a way to group functions and to organize the calculation. Using parentheses can make the formula easier to understand.
You can even use parentheses within parentheses to help build an order of succession. When parentheses are nested, the processes begin with the innermost set - and then work their way out.
Your parentheses must balance. For every open parentheses there must be a corresponding close parentheses. If the parentheses don't match, Excel will send an error message!
Examples of simple formulas in Excel:
| Excel Formula | Mathematical Formula | Excel Formula | Mathematical Formula | |
|---|---|---|---|---|
= A2 + B3 |
Addition of 2 cells | = A2 – B5 | Subtraction of 2 Cells | |
| = A5 + 25 | Addition of a Constant (25) and a Cell | = A5 – 25 | Subtraction of a Constant (25) and a Cell | |
| = SUM (A1:E1) | Addition of a Row of Cells (or a Range of Cells) | |||
| = SUM (A1,B5,G9) | Addition of Scattered Cells | |||
| = A4 * 5 | Multiplication by a Constant (5) | = A5 / 5 | Division of a Cell by a Constant | |
| = A6*B52 | Multiplication of Two Cells | = A5 / D8 | Division using 2 Cells | |
| = A6 * .60 | Multiplication by a decimal | |||
| = A6 * 25% | Multiplication by a % | |||
| = A1 + (A1 * .25) |
Increasing by a Percentage (25%) |
= A1 - (A1 *.25) |
Decreasing by a Percentage (25%) |
|
| = A1 * 1.25 | Increasing by a Percentage (25%) | =A1 *.75 | Decreasing by a Percentage (25%) | |
| = A1 + (A1 * 25%) | Increasing by a Percentage (25%) | = A1 – (A1 * 25%) | Decreasing by a Percentage (25%) | |
| = AVG (A4:C6) | Average of a Range | |||
| = AVG (A4, B5, C9, D55) | Average of 4 cells | |||
| = Sheet5!A4 | Referring to the contents of cell A4 in Sheet5. | |||
Sum and AutoSum
SUM (adding numbers to get a total - remember grade school math) is Excel's most used function.
To add number by selecting them individually
(if they are not together in a row or column)
- Click in the cell where you want your total
- Enter the = sign into the Formula Bar
The toolbar shortcut for SUM is the AutoSum button on the standard toolbar. 
Using AutoSum
- Select the cell directly beneath or to the right of a column or row of data that you want to total.
- Click the AutoSum button
- Excel will automatically insert the entire SUM formula for you and suggests a range of cells to add.
- If the suggested range is incorrect, Click on the first cell of the correct range and drag through the correct range, and press Enter.
Dates & Times
Excel treats dates and times as numbers. This means you can do math equations on them. You do have to format the cells as date or time to get the correct result!
Example, subtract two dates to find the number of days in between.
Formula
Result ="6/8/2006"-"5/27/1996"
3664 ="1:22 pm"-"9:00 am"
4:22
Quick Check Using AutoCalculate
- The AutoCalculate feature performs a simple calculation on a selected range of cells.
- The AutoCalculation appear on the bottom status bar of the Excel Program Window.
- These results are temporary and are not saved in the worksheet.
- The default AutoCalculation is SUM
- For more AutoCalculate functions, Right Click on the AutoCalculate box >> select from drop-down menu
AutoCalculate functions and their results:
| Function | Result | |
| None | Does not display a number |  |
| Average | Displays the average of the selected cells | |
| Count | Displays the number of entries made into the selected cells | |
| Count Nums | Displays the number of numeric entries made into the selected cells | |
| Max | Displays the highest value of the selected cells | |
| Min | Displays the lowest value of the selected cells | |
| Sum | Displays the sum of the selected cells |