Create Presentation
Download Presentation

Download Presentation
## Excel Functions and Formulas

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Excel Functions and Formulas**Marty Williams Training Specialist**Fundamentals**• All formulas start with the equal sign = • All functions must have parenthesis • Parentheses must match ( ) • Separate function arguments by a comma, with no space • All formulas do NOT require parenthesis but parenthesis may be necessary to calculate the right answer • Remember the order of precedence: • Expressions in parenthesis • Multiply and Divide before Add and Subtract • Left to right**Entering Formulas**• Enter manually by typing • Enter cell addresses by pointing with mouse – preferred • Enter using the formula palette – click the equal sign • Use named ranges (cell ranges that have been named) to make formulas more readable • Use a line break to make formulas more readable (Alt+Enter or Opt+CMD+Enter) • Must use ENTER or green check to finish formula**Editing Formulas**• Double click the cell to edit directly in the cell • Press F2 to edit directly in cell • Click in the formula bar to edit there • Click the equal sign to activate the Formula Palette**Absolute Reference (Value)**• Use the dollar sign ($) notation to prevent the cell reference from changing when performing a copy. • Use F4 or CMD T to assist adding the “$” • Make your worksheets more flexible: • When a formula requires a value for calculation, do not enter the value directly into the formula. • Enter the value into a cell; reference the cell in the formula • Use absolute reference to prevent the cell address from changing during a copy**Examples of Absolute Reference**• You have last years budget and you want to calculate worst, probable, and best case • Budget numbers are in column C • Possible increases are 3%, 5%, and 8% • Put these values in cells D1, E1, F1 respectively • Use absolute reference to build the formulas: • =$C2*(1+D$1) • copy this down and then across**Formula Facts**• To convert a single formula to its value, edit the formula and press F9 • To convert a range of formulas to their values, first copy them, then use EditPaste Special, select Values • Sheet names are differentiated from cell addresses with the exclamation point (!). • File names are indicated with brackets[MyFile.xls]sheet1!A1**Formula Facts, continued**• Hide formulas using • FormatCells ProtectionHidden • ToolsProtectionProtect sheet • The formula cell has the same number format as the first cell to which it refers, except when the first cell is %. • Formula limit is 1,024 characters**Named Ranges**• Name a cell or cell range for easy reference • Names use absolute reference as address, I.e., cell named TaxRate is used as $C$4 • To name a cell or range: • Select the cells • Use InsertNameDefine • Shortcuts: Ctrl+F3 or Command+F3 to define, F3 to paste name, F5 to go to a name.**Named Ranges, Part 2**• Use the Name Box in upper left corner to name selected cells • Use InsertNameCreate to quickly name several cells with one command • Use the text adjacent to cells as the cell names • Highlight both the text and the data and then issue command above • You can also name an entire row or column**Named Ranges Part 3**• Be aware of scope: name a range and it is available for the whole workbook • To list names, go to an empty area, use InsertNamePaste, List Names • To delete a name, use InsertNameDefine, select name, click Delete • Deleting a name used in a formula renders the formula invalid (#NAME?). Use UNDO**Functions**• Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, or structure • Every function has parenthesis, even when there are no arguments: rand(), today() • Function arguments are separated by commas • Functions can have no arguments, a fixed number, an indeterminate number, or optional arguments • Example: =IF(A2>100,”Wow”,”Too Bad”) =SUM(B2:B15,G4:G23)**Functions, Part 2**• Arguments can be literal values , expressions (mini formulas), other functions (nested), or arrays • Enter functions by typing directly, or use Paste Function • Case does not matter, Excel converts to uppercase when entered correctly • Paste Wizard accessed from the toolbar, InsertFunction, or Shift+F3**Review**• Do all formulas require parentheses? • No • Which happens first: add or multiply • Multiply • What do all formulas start with? • Equal sign • Cell B3 contains the mortgage rate. What is another way of identifying that cell? • By naming it**Holiday List**• Functions used: • =SUM(range) – sums a range • MIN(range) – finds the smallest value in a range • MAX(range) – finds the largest value • AVERAGE(range) – finds the average**Holiday List – more functions**• Use COUNTIF to count the number of Items for each person • =COUNTIF(range,comparison value) • =COUNTIF($A$3:$A$16,A23) • Use SUMIF to sum the items for each person • =SUMIF(range,comparison value, range of numbers to sum) • =SUMIF($A$3:$A$16,A23,$C$3:$C$16)**Holiday List – even better**• Use SUBTOTAL if you needs subtotals and a grand total. • =SUBTOTAL(range, type of aggregate desired) • =SUBTOTAL(C2:C5,9) • Can also be used for average, min, max, standard deviation**Quebec Trip**• Functions • IF(true/false question, true action, false action) • Counta(range) – counts all entries • Today() – displays the current date • Formula using single cell • Named Range**Men's Stores Sales**• Absolute Reference • Functions • Min • Max • Match • Index**Commission Report**• Modify multiple worksheets at once • Range Names expanded • Functions • If • Vlookup • Round**Bakery**• Use cascading formula to generate weekly dates, and have the dates flow from one sheet to the next • Sum using 3D reference**Acme – text manipulations**• Text manipulations using “&” • Paste special as values • String functions • Proper • Mid • Left • Right • Len • Val**Payment functions**• PMT – mortgage payment • IPMT – Interest portion • PPMT – principle portion • NPR – number of payments required when paying a constant amount • Other Finance Functions – use the Paste Function Wizard**Functions**Sum Average If Absolute Reference Spread percentages Multiple worksheets with links Paste Special Paste links Paste formats Paste values Named Ranges Household Budget**Date Functions**• Many functions available for data math and manipulations. • Use “Date and Times.xls”**More on Named Ranges**• Don’t use “natural language formulas” • Use a space to show intersection of two names for explicit intersection: =January Amount would return the value of the cell where the January range intersects the Amount range • Implicit intersection is determined by location of active cell adjacent to a name range: When $A$1:$A$9 is named MyData, then when active cell somewhere on row 5 contains the formula =MyData, the value returned is the value in A5**More on Names Ranges, 2**• Use the range indicator, a color, to indicate two named ranges: =sum((Region1 January):(Region2 March)) note use of parentheses. • Use InsertNameApply to retroactively apply range names to formulas. • Names are automatically used when they already exist. • Use EditReplace to replace one name with another • Use Zoom Out to 39% or less to view named ranges