Lesson 2
Working with Formulas and Functions
Functions: are predefined worksheet formulas that Excel supplies for you. A few predefined are:
Sum, Average, Count Numbers, Max, Min
Formulas consist of the following elements:
Math Operators, Cell references, Values or text, Worksheet functions
A formula is a series of mathematical instructions that you place in a cell. Formula results are usually numbers, although you can create formulas that have text or BOOLEAN (true or false) results.
Formulas can be simple or complex, but in all cases they share the same basic characteristics:
- YOU ENTER EACH FORMULA IN TO A SINGLE CELL
- FORMULAS CAN EVALUATE A COMBINATION OF NUMBERS
- EXCEL CALCULATES THE RESULT OF A FORMULA EVERY TIME YOU OPEN A SPREADSHEET OR CHANGE THE DATA
- TO VIEW A FORMULA, YOU HAVE TO SELECT THE CELL CONTAINING THE FORMULA AND LOOK AT THE FORMULA BAR, WHERE YOU CAN ALSO EDIT A FORMULA
After you enter a formula, the cell displays the formula's calculated result. the formula itself appears in the formula bar when you select the cell.
SIMPLE formulas use basic arithmetic operators: +, *, -, or /. Example:
=150*.05 Multiplies 150 times .05
=SUM(A1:A2) Adds cells A1 through A12
=Income-Expenses Subtracts the value of Expenses from the value of Income
Example of COMPLEX formulas: (will not be on Quiz)
="Part-"&"23A" Joins the two text strings to produce this: Part-23A
=A1<=A2 Returns TRUE if the value in cell A1 is less than or equal
=B1< > B2 Returns TRUE if the value in cell B1 isn't equal to the value in B2
ORDER OF OPERATIONS
Order of precedence determines the order in which the operations are calculated.
- Values inside parentheses are calculated before any other operations
- Reference operators are calculated first
- Exponents are calculated next
- Multiplication and division are calculated from left to right
Relative & Cell References
Copying a cell allows you to reuse formulas that you've already created. The Copy and Paste can be used to copy a formula. The FILL Down option can be used for rows or FILL right for columns or both to fill in formulas.
Difference between relative and absolute in EXCEL:
- Relative cell references tell Excel how ti find another cell starting from the cell that contains the formulas. When you use a relative reference, it's like giving someone driving directions. There's a starting point (the current location) and a final destination point. When a formula containing relative references is moved, it will reference new cells based on their location in relation to the formula. Relative references are the default type of reference.
- Absolute cell references refer to the same cell address, even when the formula is moved.
- Mixed cell references combine both elements of absolute and relative references.
How relative references work:
- Next, when cell C2's formula is
copied to C2:C11, the formula is calculated for the row that it's in.
For example, in C11 it's calculated as =SUM(A11:B11). Because the
formula calculates the values in its row, the cell references are
relative.
- The formula =SUM(A2:B2) is applied to cells A2 and B2, and the result is displayed in cell C2.
ROUNDING VALUES
Excell allows you to round off values. Highlight the cell or cells of values you want to round off, and, under the FORMULAS tab and in the FUNCTION LIBRARY group, select MATH & TRIG. Scroll down to the ROUND function.