MS Excel Learning Classes – 5

Formulas and Functions

The distinguishing feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions. Otherwise, it is not much more than a large table for displaying text.

Formulas

A formula is a set of mathematical instructions that use cell references and operators to answer a question about data stored in the spreadsheet. For example, “How much have we spent on office supplies so far this year?” or “How much would it cost to buy everyone in my work group a new computer?”

• A formula is a sequence of values, cell references, names, functions, or operators that produces a new value from existing values.
• Formulas are entered in the worksheet cell and must begin with an equal sign “=”.
• The formula then includes the addresses of the cells whose values will be manipulated with appropriate operands placed in between.
• After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the formula bar.
• A formula will display on the screen as a number but the formula bar will show what is really stored in that cell.
• As numbers affecting the formula change, the formula value will change.

Formula Components

• A formula is composed of cell references, values and mathematical operators.
• A cell reference refers to the spreadsheet cell address, which is indicated by a column letter and a row number. The cell at the intersection of column C and row 50 is referred to as cell C50.
• A formula may contain a numeric value. Constants may be used in formluas when the value isn’t stored in the spreadsheet.
• Mathematical operators refers to the symbol used to indicate a mathematical function.

Mathematical Operators

Operators are the method of mathematical function used to return the desired output such as Plus, Minus, Divided by, etc. Below is a listing of the common Operators used in Excel:

In the Real Example column shown above there are values, but on a spreadsheet the formula can use cell references that change value with user input without having to edit the formula. A call reference is a placeholder, in this case a value entered into a cell. This means that instead of having to manually update the values in a formula every single time a user inputs data, the formula works with whatever data is stored in its reference or cell area.

For example if we created a simple spreadsheet and wanted to figure our state’s sales tax for every transaction we would have to multiply the dollar amount in the Gross Cost area (cell C1) by the tax amount (5%) and display the results in cell D1.

The formula in cell D1 might look like this:

=C1*5%

To change a formula once it has been entered you may double-click in on the cell that contains the formula you wish to edit or single click and it will appear in the Formula bar.

Order of Operations

Do you remember the rules of the order of operations from grade school? Excel sure does. In fact, this is one of the most common things overlooked by most users new to Excel.

If you wanted to add the numbers in cells B4, B5 and B6 and then multiply them by the data in cell B7 you may try to do it in the way that you think is common sense, such as = B4 + B5 + B6 * B7, however that will not get you the desired result. Instead, Excel will follow the old order of operations rule and interpret that formula as Multiply the contents of B6 * B7 then add the result to the numbers in B4 and B5.

To get around this we use parentheses to alter the order of operations, knowing that operations within the parentheses will be done first in order from left to right. Here’s how the formula should look to attain the desired result:

= (B4 + B5 + B6) * B7

Always organize multiple cell references within parentheses to control the order of operations.

Using AutoSum

Excel understands that the most common formula operation involves adding a range of cells to get a sum. For this reason they have given us a great shortcut that allows us to add as much as we like with minimal effort. Just do this:

1. Select all of the cells that you would like to have added together.
2. Click the AutoSum button on the standard toolbar.

The sum is shown in the cell below or adjacent to those added.

The Basic Principles

1. Formulas can use cell references as variables (e.g., =A1+B2 means the value at cell A1 is added to the value at cell B2).
2. The formula automatically recalculates the results when you change any of the values (ie the data) that the formula uses.
3. A formula must begin with = equals sign.
4. If a formula uses a function, it must begin with = equals sign, followed by a function name, followed by the function’s arguments in brackets.
5. Excel processes the operators in the formula in a particular order, starting from the top.
6. Use parentheses (round brackets) to alter the order in which Excel processes operators. Excel calculates the expression in brackets first, then uses the results to recalculate the formula. For example:

7. There is a difference between the display of figures and the figures used for calculation, eg 25.6456 is the figure used for calculation even though in currency format Excel displays $25.65.