Excel Functions and Formulas

1. Excel Functions and Formulas Marty Williams Training Specialist

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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 EditPaste Special, select Values • Sheet names are differentiated from cell addresses with the exclamation point (!). • File names are indicated with brackets[MyFile.xls]sheet1!A1

8. Formula Facts, continued • Hide formulas using • FormatCells ProtectionHidden • ToolsProtectionProtect 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

9. 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 InsertNameDefine • Shortcuts: Ctrl+F3 or Command+F3 to define, F3 to paste name, F5 to go to a name.

10. Named Ranges, Part 2 • Use the Name Box in upper left corner to name selected cells • Use InsertNameCreate 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

11. 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 InsertNamePaste, List Names • To delete a name, use InsertNameDefine, select name, click Delete • Deleting a name used in a formula renders the formula invalid (#NAME?). Use UNDO

12. 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)

13. 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, InsertFunction, or Shift+F3

14. 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

15. Worksheet Examples

16. 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

17. 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)

18. 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

19. 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

20. Men's Stores Sales • Absolute Reference • Functions • Min • Max • Match • Index

21. Commission Report • Modify multiple worksheets at once • Range Names expanded • Functions • If • Vlookup • Round

22. Bakery • Use cascading formula to generate weekly dates, and have the dates flow from one sheet to the next • Sum using 3D reference

23. Acme – text manipulations • Text manipulations using “&” • Paste special as values • String functions • Proper • Mid • Left • Right • Len • Val

24. 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

25. Functions Sum Average If Absolute Reference Spread percentages Multiple worksheets with links Paste Special Paste links Paste formats Paste values Named Ranges Household Budget

26. Date Functions • Many functions available for data math and manipulations. • Use “Date and Times.xls”

27. 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

28. 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 InsertNameApply to retroactively apply range names to formulas. • Names are automatically used when they already exist. • Use EditReplace to replace one name with another • Use Zoom Out to 39% or less to view named ranges