LectureFormulasCreating FormulasAlgebraic Order of OperationsOrder of Operation “Please Excuse My Dear Aunt Sally”FunctionsFunction SyntaxRanges and ArgumentsFunction ExamplesCommon FunctionsFunction Syntax (cont)List of Common FunctionsShortcut Functions/MoreRelative and Absolute AddressingAbsolute References!!!Quick Way to SwitchLectureExcel: Formulas and FunctionsFormulas•Specifies calculations to be performed•Begins with an equal sign (=)•Can refer to:–cells by reference (A1)–values (25%, 100, 0.50)•Recalculates when values change•Example: = A1 + B2•Without the equal sign, it's just a labelCreating Formulas= C9 times timesD9 E9= 450Value(calculated results)Algebraic Order of Operations•Parentheses ( ) (5+3)/2 4•Exponentiation ^ 5^2 25•Multiplication * 5*2 10•Division / 5/2 2.5•Addition + 5+2 7•Subtraction - 5-2 3Order of Operation“Please Excuse My Dear Aunt Sally”•Parentheses•Exponents•Multiply and Divide•Add and Subtract=10 + 2 * 3 =16=(10 + 2) * 3 =36Functions•Built-in ways to manipulate data to get an answer (sum, average, etc)•Give it numbers (or text) and get an answer•Values you provide the function are called "arguments"•Parentheses enclose the argument list–Cells or other expressions needed for the calculationsFunction Syntax•Syntax of Excel Function= FUNCTION(argument)•Functions can be part of an expression= FUNCTION(arg) * 75%Ranges and Arguments•Some functions take ranges=SUM(A1:A10)=MAX(A1:D50)•Some functions take multiple arguments=ROUND(A1,2)(rounds A1 to 2 decimal places)Function Examples=SUM(D14:D18)=MAX(B6:B13)=AVERAGE(A1:A8)Function Name ArgumentCommon Functions•=SUM(cell range)–Total of all cells listed as arguments•=MIN(cell range)–Smallest number within the cell range named•=AVERAGE(cell range)–Total of all the cells divided by the total number of cells listedFunction Syntax (cont)•Functions can be combined= FUNCTION(arg) + FUNCTION(arg)•Functions can be nested= FUNCTION(FUNCTION(arg))List of Common Functions•=AVERAGE( )•=SUM ( )•=MIN ( )•=MAX ( )•=NOW ( )•=TODAY ( )•=COUNT ( )•=COUNTA ( )•=COUNTIF ( )•=COUNTBLANK ( )•=ROUND ( )•=ROUNDUP ( )•=ROUNDDOWN ( )Shortcut Functions/More•Shortcut functions from Sum function drop-down button•More Functions by category–Statistical–Math & Trig–Financial–Database–etcRelative and Absolute Addressing•relative address B4•absolute address $B$4Anchors row or columnAbsolute References!!!=B2*$B$8Quick Way to Switch•Type formula in with relative addresses (=B4 * E5)•Place cursor at reference you want made absolute•Press F4:B4  $B$4  B$4  $B4  B4•Mac users: