MS-Excel / Functions and Formula

---

Creating a Formula

Formulas consist of three crucial bits of information:

• An equal sign (=)
• One or more cell references
• The type of calculation to do on the data (addition, subtraction, and so on)

A cell reference is simply the unique row and column heading that identifies a single cell, such as A4 or D9. The four common calculations that a formula can use are addition (+), subtraction (-), multiplication (*), and division (/).

Table lists show other mathematical operators you can use in a formula.

Common Mathematical Operators Used to Create Formulas

Operator	What It Does	Example	Result
+	Addition	=5+3.4	8.4
-	Subtraction	=54.2-2.1	52.1
*	Multiplication	=1.2*4	4.8
/	Division	=25/5	5
%	Percentage	=42%	0.42
^	Exponentiation	=4^3	64
=	Equal	=6=7	False
>	Greater than	=7>2	True
<	Less than	=9<8	False
>=	Greater than or equal to	=45>=3	True
<=	Less than or equal to	=40<=2	False
<>	Not equal to	=5<>7	True
&	Text concatenation	="Bo the "& "Cat"	Bo the Cat

A simple formula uses a single mathematical operator and two cell references such as:

=A4+C7

This formula consists of three parts:

• The = sign: This identifies your formula. If you type just A4+C7 into a cell, Excel treats it as ordinary text.
• Two cell references: In this example, A4 and C7.
• The addition (+) mathematical operator.

To type a formula in a cell, follow these steps:

1. Click in the cell where you want to store the formula.
   You can also select a cell by pressing the arrow keys. Excel highlights your selected cell.
2. Type the equal sign (=). This tells Excel that you are creating a formula.
3. Type your formula that includes one or more cell references that identify cells that contain data, such as A4 or E8.
   For example, if you want to add the numbers stored in cells A4 and E8, you would type =A4+E8.
4. Press Enter.

Typing cell references can get cumbersome because you have to match the row and column headings of a cell correctly. As a faster alternative, you can use the mouse to click any cell that contains data; then Excel types that cell reference into your formula automatically.

To use the mouse to click cell references when creating a formula, follow these steps:

1. Click in the cell where you want to store the formula. (You can also select the cell by pressing the arrow keys.) Excel highlights your selected cell.
2. Type the equal sign (=).
   This tells Excel that anything you type after the equal sign is part of your formula.
3. Type any mathematical operators and click any cells that contain data, such as A4 or E8.
   If you want to create the formula =A4+E8, you would do the following:
   
   • Type =.
     This tells Excel that you're creating a formula.
   • Click cell A4.
     Excel types the A4 cell reference in your formula automatically.
   • Type +.
   • Click cell E8.
     Excel types in the E8 cell reference in your formula automatically.
4. Press Enter. After you finish creating a formula, you can type data into the cell references used in your formula to calculate a new result.

Operator Precedence in Excel

Mathematical Operator	Description
: (colon) (single space), (comma)	Reference operators
-	Negation
%	Percent
^	Exponentiation
* and /	Multiplication and divisions
+ and -	Addition and subtraction
&	Text concatenation
= < > <= >= <>	Comparison

Typing parentheses around cell references and mathematical operators not only organizes your formulas but also tells Excel specifically how you want to calculate a formula. In the example =A3+A4*B4+B5, Excel multiplies A4 and B4 first. If you want Excel to first add A3 and A4, then add B4 and B5, and finally multiply the two results together, you have to use parentheses, like this:

=(A3+A4)*(B4+B5)