Home > Articles > Home & Office Computing > Microsoft Applications

This chapter is from the book

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)

Subtraction
Negative

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<B7

>= (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

  • + Share This
  • 🔖 Save To Your Account