IS113 Topic 4: Introducing Formulas & Functions

Circular reference

when a formula refers back to itself. For example, entering A1+B1=C1 in A1 Excel cannot solve formulas with circular references with normal calculation

Ways of copying a formula

1. Copy and Paste buttons 2. Click the cell that contains the formula. Press Ctrl key and drag cell's border to the cell you want to copy the formula to and release the mouse button 3. To copy a formula to neighbouring range of cells: a) Click the cell that contains the formula b) Move the mouse over the fill handle c) Drag the fill handle across the cells into which you to copy the formula

Types of Cell References

1. Relative Reference 2. Absolute Reference 3. Mixed Reference

Building functions with paste function dialogue box

1. Select the cell 2. Click the Paste Function which is near the formula bar or go to Formulas tab, Function Library group of commands, click on the Insert Function command 3. Select the function 4. Specify arguments for the function in the Formula Palette 5. Click OK

Pointing to Cell referencing to enter formulas

1. Select the cell 2. Type equal sign 3. Click the cell (for single cell) 4. or Drag over the range whose reference you want to appear in the formula (for range of cells) 5. Type mathematical formula 6. Continue clicking cells and typing operators until the formula is complete 7. Press Enter The cell normally displays the formula's result. When you select the cell containing the formula, formula is displayed in the formula bar.

Entering Formulas Manually

1. Select the cell in which you the formula result to appear 2. Type the formula 3. Press Enter or Click Enter button on the formula bar. The result appears

Editing Formulas

1. Select the cell that contains the formula 2. Make changes in the formula bar or, 3. Double-click the cell and makes changes directly in the cell, or 4. Select the cell and then press F2 to enter edit mode. Then make changes.

Absolute Reference

A cell reference in a formula that does not change when the formula is copied to new location Sometimes you don't want excel to automatically change cell references to reflect their new positions. Use absolute reference to refer to the same specific cell on the worksheet in every copy of the formula. Denoted with a dollar sign ($) before the column and row references, such as $C$9 E.g. copying a formula with absolute reference $C$9 to another cell does not change the reference. Copying formula with relative reference C9 changes the reference.

Relative Reference

A cell reference in a formula that is changed when the formula is copied A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2. For example, if cell C9 contains the formula = C4+C5+C6+C7+C8. When this formula is copied to cell D9, excel automatically adjusts the formula to =D4+D5+D6+D7+D8. The row and column references change when the formula is copied.

Error values

Error values are displayed if formula cannot properly display a result

Formulas

Formulas help you perform operations such as addition, multiplication, comparison of worksheets, etc. Formula can consist of: a) Mathematical and logical operators b) Cell references (including named cells and ranges) c) Values or text d) Functions such as SUM or AVERAGE

Mixed Reference

Partially absolute reference A mixed reference has either an absolute column and relative row, or absolute row and relative column. An absolute column reference takes the form $A1, $B1, and so on. If the position of the cell that contains the formula changes, the relative reference is changed, but the absolute reference does not change. If you copy the formula across rows or down columns, the relative reference automatically adjusts, and the absolute reference does not adjust. For example, if you copy a mixed reference from cell A2 to B3, it adjusts from =A$1 to =B$1 When a formula containing a mixed reference is copied to another cell, part of cell reference (the relative part) is adjusted

Changing Reference Types

Reference types can be changed by: Manually enter references or use the F4 key. Note: when you name a cell or range, Excel uses absolute reference for the name. If you copy a formula that has a named reference, the copied formula contains a reference to the original name E.g. Assume a formula =K4. Pressing F4 coverts the reference to =$K$4. Pressing F4 again converts it to K$4. Pressing F4 again converts it to $K4. Pressing again results in the original reference (K4)

Using Cell References in Formulas

References identify cells or groups of cells on a worksheet. References tell Excel which cells to get values from and to be used in formulas. When the values in those cells change, the result of the formula changes to reflect the new values in the cell. If other values, such as constants, are used in the formula, you have to edit the formula to change the values For example, if cell A1 has value 5 and cell B1 has value 9, the formula =A1+B1 gives 14. If value in A1 or B1 is changed, the result automatically changes.

Referencing Cells in other worksheets/workbook

Referencing cells in other worksheets 1. Formulas can also refer to cells in other worksheets. 2. Format is SheetName!CellAddress E.g. B4 + Sheet1!C4 adds the value in cell B4 on the current worksheet and the value in cell C4 in another worksheet called Sheet1 Referencing cells in other workbooks 1. Format is [WorkbookName]SheetName!CellAddress E.g. B4 * Sheet1!C4 * [Summary.xls]Sheet1!D5 multiplies the value in cell B4 on the current worksheet with the values in cell C5 on Sheet1 and value in cell D5 on Sheet1 of summary workbook.

Using AutoSum

Using AutoSum: 1. Select the cell in which you want to insert the total 2. AutoSum guesses which cells to sum. If selected cells are incorrect, edit the selection 3. Click the AutoSum button on the Home tab or Formulas tab 4. Press Enter

Copying Formulas

When you copy a formula, the formula is adjusted to fit the location of the cell to which it is copied. When you copy a formula, the cell references may change based on the type of reference used. For example, copying the formula =A1+A2 from cell A3 to B3, it is adjusted so that it becomes =B1+B2

Moving Formulas

When you move a formula, cell references within the formula do not change To move a formula: 1. Select cell that contains the formula you want to move 2. Point to border of selected cell and drag to new location You can also use the Cut and Paste methods

Basic Concepts of a formula

a) Formulas start with equal sign (=) b) Add parenthesis to control order of operators c) Formula appears on the formula bar. Formula result is displayed in the cell d) Formula appears in both formula bar and cell when editing

General syntax of a function

a) Function Name (argument 1, argument 2,.....) b) Function starts with function name, opening parenthesis, arguments separated by comma, and closing parenthesis. c) Arguments are values function uses to perform calculations. They can be numbers, text, cell references, names, formulas, or other functions. For example, to calculate a interest on loan and add it to the loan (with an interest rate of 5%), type = SUM(B3+B3*0.5)

Reference Operators

combine ranges of cells for calculation with these operators: Range Operators; and Union Operators

"," Union Operator

combines multiple references into one reference Example; SUM(A1:A7 , A1:A7)

Comparison Operators

perform logical operations by comparing two values. Result is ether TRUE or FALSE.

Arithmetic Operators

performs basic mathematical operations

Functions

pre-defined or built-in formulas that perform calculations by using specific values, called arguments, in a particular order or structure

":" Range Operator

produces one reference to all the cells between two references, including the two references Example, A1: A7

Order of Operations

set of predefined rules that Excel uses to calculate a formula by determining which part of the formula to calculate first, which part second, and so on.

Text Concentration Operator

use the ampersand (&) to join, or concatenate one or more text strings to produce a single piece of text. For example "North" & "east" produces Northeast