IT01PEBL: Chapter 4: Introducing Formulas and Functions | University of Rwanda

4.1. Understanding Formula Basics

A formula consists of special code entered into a cell. It performs a calculation of some type and returns a result, which is displayed in the cell.

Formulas use a variety of operators and worksheet functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature.

A formula can consist of any of these elements:

Mathematical operators, such as + (for addition) and * (for multiplication)
Cell references (including named cells and ranges)
Values or text
Worksheet functions (such as SUM or AVERAGE)

After you enter a formula, the cell displays the calculated result of the formula. Here are a few examples of formulas:

Note that every formula begins with an equal sign (=). The initial equal sign allows Excel to distinguish a formula from plain text.

Using operators in formulas

Excel formulas support a variety of operators. Operators are symbols that indicate what mathematical operation you want the formula to perform.

Table below lists the operators that Excel recognizes:

Understanding operator precedence in formulas

When Excel calculates the value of a formula, it uses certain rules to determine the order in which the various parts of the formula are calculated. You need to understand these rules so your formulas produce accurate results.

This table below shows that exponentiation has the highest precedence (performed first) and logical comparisons have the lowest precedence (performed last).

You can use parentheses to override Excel’s built-in order of precedence. Expressions within parentheses are always evaluated first. For example, the following formula uses parentheses to control the order in which the calculations occur. In this case, cell B3 is subtracted from cell B2, and the result is multiplied by cell B4:

=(B2-B3)*B4

If you enter the formula without the parentheses, Excel computes a different answer. Because multiplication has a higher precedence, cell B3 is multiplied by cell B4. Then this result is subtracted from cell B2, which isn’t what was intended. The formula without parentheses looks like this:

=B2-B3*B4

4.2. Using functions in your formulas

Examples of formulas that use functions

A worksheet function can simplify a formula significantly. Here’s an example. To calculate the average of the values in ten cells (A1:A10) without using a function, you’d have to construct a formula like this:

= (A1+A2+A3+A4+A5+A6+A7+A8+A9+A10)/10

you can replace this formula with a much simpler one that uses one of Excel’s built-in worksheet functions, AVERAGE:

=AVERAGE (A1:A10)

Say you need to determine the largest value in a range. A formula can’t tell you the answer without using a function. Here’s a formula that uses the MAX function to return the largest value in the range A1:D100:

=MAX (A1:D100)

Function with conditions

If the salesperson sold more than RWF300,000 of product, the commission rate is 7.5 percent; otherwise, the commission rate is 5.0 percent. Without using a function, you would have to create two different formulas and make sure that you use the correct formula for each sales amount. A better solution is to write a formula that uses the IF function to ensure that you calculate the correct commission, regardless of sales amount:

=IF (A1<300000, A1*5%, A1*7.5%)

This formula performs some simple decision making. The formula checks the value of cell A1. If this value is less than 300,000, the formula returns cell A1 multiplied by 5 percent. Otherwise, it returns what’s in cell A1 multiplied by 7.5 percent. This example uses three arguments, separated by commas.

4.3. Entering Formulas into Your Worksheets

Every formula must begin with an equal sign to inform Excel that the cell contains a formula rather than text. Excel provides two ways to enter a formula into a cell: manually, or by pointing to cell references.

Excel provides additional assistance when you create formulas by displaying a drop-down list that contains function names and range names.

Referencing cells outside the worksheet

Formulas can also refer to cells in other worksheets — and the worksheets don’t even have to be in the same workbook. Excel uses a special type of notation to handle these types of references.

Referencing cells in other worksheets

To use a reference to a cell in another worksheet in the same workbook, use this format:

SheetName!CellAddress

In other words, precede the cell address with the worksheet name, followed by an exclamation point. Here’s an example of a formula that uses a cell on the Sheet2 worksheet:

=A1*Sheet2!A1

Referencing cells in other workbooks

To refer to a cell in a different workbook, use this format:

=[WorkbookName]SheetName!CellAddress

Example:

=[Budget.xlsx]Sheet1!A1