Download as PDF, PPTX
Uploaded byLearnIT@UD
Excel functions formulas
This document provides an overview of using formulas and functions in Excel 2007, including mathematical operators, cell references, common functions like SUM and IF, finding the right function, fixing errors, naming cells, and other tips. Key topics covered are basic formulas, relative and absolute cell references, using functions, the IF function, displaying formulas, the function library, and identifying common errors. Users will learn the basics of working with formulas and functions in Excel.
More Related Content
Similar to Excel functions formulas
Excel functions formulas
- 1. LearnIT@Lunch Understanding Formulas and Functions In Excel 2007 IT-User Services
- 2. Using Formulas During thissession you will learn about: • Basic mathematical operators • Relative and absolute cell references • Using Functions • Using the IF function • Finding the right function • Fixing errors • Working with range name
- 3. Formula vs. Function Formulasare equations that perform calculations on values. Functions are predefined formulas that perform calculations by using specific values in a certain order.
- 4. Mathematical Operators Basic MathematicalOperators Control order of operations with ( )
- 5. Mathematical Operators Use Parenthesesto control operations
- 6. Exploring Functions Function syntax =FunctionName(Argument1,Argument2, Agrument3) Useful & Simple Functions =SUM =AVERAGE =MIN =MAX
- 7. Exploring Functions =SUM (rangeor arguments)
- 8. Copying Formula Relative References Whena formula is copied, the referenced cells changes *Note: The result of cut and paste is different than copy and past
- 9. Copying Formula Absolute References •Use$ to ‘lock’ the row/column reference •Press (F4) to create the ($) sign
- 10. Copying Formula Quick Tips •Use Ctl + Enter •Double click the Fill Handle
- 11. AutoCalculate Using AutoCalculate Calculations doneby Excel on range of data
- 12. Display Formulas Displaying andPrinting Formulas Formulas Tab, Show Formulas button
- 13. Using the FunctionLibrary Inserting Functions Type into formula bar or use the function library
- 14. Finding Functions AutoComplete (newin Excel 2007) Type = then first letter of the function
- 15. Finding Functions Function Wizard Typekey words search or browse library
- 16. Using IF Function IFFunction syntax =IF (test, if value true, if value false)
- 17. VLOOKUP Syntax =VLOOKUP(J2,$N$1:$O$4,2,FALSE) To Find an Exact Match J2 = Lookup value (last 4 digits of pro card) $N$1:$O$4 = Table array (pro card # with employee name) 2 = Column index from table array with value to be returned False = Tells Excel to return value if exact match is found This function works differently based on the 4th parameter.
- 18. Using Functions Working withNested Functions Functions inside other functions
- 19. Identifying Errors Error Messages #VALUE!– Trying to do math with nonnumeric data. #DIV/0! – Trying to divide by zero. #REF! – Occurs when a cell reference is not valid. (When a cell referenced in formula has been deleted.) #N/A! – Occurs when a value is not available to a function or formula.
- 20. Naming a cellor range Instead of using the cell address, give the cell or range a name (e.g. grandtotal)
- 21. Naming a cellor range • Make formulas easier to understand and use • “procardlist” is much more meaningful than A2:B5. • Quick navigation
- 22. Naming a cellor range Naming Rules • Up to 255 characters long • No spaces (may use “_” underscore) • Cannot contain special characters (!”$) • Cannot contain operators (+-%)
- 23. Naming a cellor range Defining and Using Range Names Select Formulas tab and Define Name
- 24. Helpful Resources www.udel.edu/learn www.udel.edu/help orcall 831-6000 www.datapigtechnologies.com http://office.microsoft.com/en-us/training