Create an Excel Formula

In Excel, if you want to perform a mathematical calculation such as addition, subtraction, multiplication, or division, use a formula. Type the formula in the cell where you want the results to appear. The formula should start with an equal sign, and can consist of numbers, mathematical operators, references, logical values, and functions, or any combination thereof. For now, we will stick with numbers, cell references, and mathematical operators. We will discuss each of the other options, later.

Mathematical operators tell Excel the type of mathematical calculation you want to perform. For example, the plus sign (+) tells Excel to add and the minus sign (-) tells Excel to subtract. The following Mathematical Operators table lists all of Excel's operators.

Mathematical Operators
Symbol Calculation Type Formula Result
+ Addition =1+1 2
- Subtraction =4-3 1
- Negation =-1 -1
* Multiplication =2*3 6
/ Division =6/2 3
^ Exponential (Raises to a power) =3^2 9
% Percent =10% 10%

To reference a cell, include the cell address in the formula. If the cell contains a number, Excel will use the number in the calculation. If the cell contains text that is not in a format that Excel recognizes as a number, Excel will return an error. For example, if cell A1 contains the number 1, the formula = A1+1 returns 2. If you change the number in the cell, the results of the calculation will change. For example, if you change the number in cell A1 to 2, = A1+1 returns 3. If you change the value in cell A1 to Sales, = A1+1 returns the error #VALUE.

When creating a formula, be mindful of precedence. Precedence is the order in which Excel performs calculations. Excel uses the following order:

  • Negation (-10)
  • Percent (%)
  • Power (^)
  • Multiplication and division
  • Addition and subtraction
  • Joining text (&)
  • Comparison (= < > <= >= <>)

If a formula contains operators with the same priority, Excel calculates them from left to right. To change the order in which Excel performs calculations, use parentheses. Excel will calculate the expressions in parentheses; then use the results in the formula. For example, the result of =2+3*4 is 14. Excel multiplies 3 times 4, and then adds 2. To have Excel add 2 plus 3, then multiply the result by 4, type =(2+3)*4. The result is 20.

You can enter formulas in the following worksheet. The Examples tab contains several valid formulas. Feel free to modify them. Click the Operators tab to see the Mathematical Operators table. Click and use the Practice Here tab any way you want.

Leave a Comment

HTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>