# Working with Formulas and Functions in Excel 2013

• Print
This chapter is from the book

## Understanding Formulas

### Introduction

A formula calculates values to return a result. On an Excel worksheet, you can create a formula using constant values (such as 147 or \$10.00), operators (shown in the table), references, and functions. An Excel formula always begins with the equal sign (=).

A constant is a number or text value that is not calculated, such as the number 147, the text “Total Profits”, and the date 7/22/2013. On the other hand, an expression is a value that is not a constant. Constants remain the same until you or the system change them. An operator performs a calculation, such as + (plus sign) or - (minus sign). A cell reference is a cell address that returns the value in a cell. For example, A1 (column A and row 1) returns the value in cell A1 (see table below).

#### Cell Reference Examples

 Reference Meaning A1 Cell in column A and row 1 A1:A10 Range of cells in column A and rows 1 through 10 A1:F1 Range of cells in row 1 and columns A through F 1:1 All cells in row 1 1:5 All cells in rows 1 through 5 A:A All cells in column A A:F All cells in columns A through F Profits!A1:A10 Range of cells in column A and rows 1 through 10 in worksheet named Profits

A function performs predefined calculations using specific values, called arguments. For example, the function SUM(B1:B10) returns the sum of cells B1 through B10. An argument can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #NA, or cell references. Arguments can also be constants, formulas, or other functions, known as nested functions. A function starts with the equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. For example, the function, AVERAGE(A1:A10, B1:B10), returns a number with the average for the contents of cells A1 through A10 and B1 through B10. As you type a function, a ToolTip appears with the structure and arguments needed to complete the function. You can also use the Insert Function dialog box to help you add a function to a formula.

### Perform Calculations

By default, every time you make a change to a value, formula, or name, Excel performs a calculation. To change the way Excel performs calculations, click the Formulas tab, click the Calculation Options button, and then click the option you want: Automatic, Automatic Except Data Tables, or Manual. To manually recalculate all open workbooks, click the Calculate Now button (or press F9). To recalculate the active worksheet, click the Calculate Sheet button (or press Shift+F9).

#### Precedence Order

Formulas perform calculations from left to right, according to a specific order for each operator. Formulas containing more than one operator follow precedence order: exponentiation, multiplication and division, and then addition and subtraction. So, in the formula 2 + 5 * 7, Excel performs multiplication first and addition next for a result of 37. Excel calculates operations within parentheses first. The result of the formula (2 + 5) * 7 is 49.

#### Types of Operators

 Operator Meaning Example Arithmetic = (plus sign) Addition 2+7 - (minus sign) SubtractionNegative 7-2-2 * (asterisk) Multiplication 2*7 / (forward slash) Division 7/2 % (percent) Percent 70% ^ (caret) Exponentiation 2^7 Comparison = (equal sign) Equal to A2=B7 > (greater than sign) Greater than A2>B7 < (less than sign) Less than A2= (greater than or equal to sign) Greater than or equal to A2>=B7 <= (less than or equal to sign) Less than or equal to A2<=B7 <> (not equal to sign) Not equal to A2<>B7 Text concatenation & (ampersand) Connects, or concatenates, two values to produce one continuous text value “Total”&”Profit” Reference : (colon) Range operator, which produces one reference to all the cells between two references A1:A10 , (comma) Union operator, which combines multiple references into one reference SUM(A1:A10,B1:B10) (space) Intersection operator, which produces on reference to cells common to the two references A1:A10 B1:B10