1 / 16

Error Messages, VLookup, Practical Tips

Error Messages, VLookup, Practical Tips. What use is VLookup ? How do you error check in Excel?. VLOOKUP Function . assigns a value to a cell based on a numeric value in another cell Allows Excel to look up a value in a table and return a related value

justis
Download Presentation

Error Messages, VLookup, Practical Tips

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Error Messages, VLookup, Practical Tips What use is VLookup? How do you error check in Excel? CS 105 Spring 2010

  2. VLOOKUP Function • assigns a value to a cell based on a numeric value in another cell • Allows Excel to look up a value in a table and return a related value • for example, Excel can look up your average in this class and return your grade CS 105 Spring 2010

  3. More on Vlookup • Requires three arguments: • cell (or numeric value) to look up • range of the table • the column number containing the value you want to return =VLOOKUP(lookup cell, range, answer) CS 105 Spring 2010

  4. Another Example • AI2 holds the points we want to evaluate • Range = AL:AM • We want to display the 2nd column of data CS 105 Spring 2010

  5. Approximate matching: look for 170 VLOOKUP searches the AL column for the largest value less than or equal to 170, and returns the corresponding value from column AM. CS 105 Spring 2010

  6. Multi-table lookup • Suppose we want to lookup the match for something in one table, and then lookup the match for that in another table CS 105 Spring 2010

  7. Providing User Feedback • Some functions are built into Excel to help provide feedback and test content • They could be helpful to you… CS 105 Spring 2010

  8. Examples of useful functions =ISERROR(B2) equals TRUE =ISNUMBER(B3) equals FALSE Formula view: CS 105 Spring 2010

  9. Examples of useful functions • =ISTEXT(B4) equals TRUE • =ISBLANK(B5) equals TRUE • Formula view: CS 105 Spring 2010

  10. How to use these functions • You want to calculate the average of the range C1:C4, but you can't be sure that the cells contain numbers. • The formula AVERAGE(C1:C4) returns #DIV/0! if C1:C4 does not contain any numbers. Use: =IF(ISERROR(AVERAGE(C1:C4)),"No Numbers",AVERAGE(C1:C4)) CS 105 Spring 2010

  11. Importing Data into Excel • You can import data and not have to retype it. • Excel can create spreadsheets from Word tables • You can import and export spreadsheets and databases CS 105 Spring 2010

  12. Importing Data into Excel 2007 CS 105 Spring 2010

  13. CS 105 Spring 2010

  14. Error Messages • ##### Column not wide enough, with numbers will convert to scientific notation, otherwise you will see ### • #NAME? Perhaps a cell reference is not valid: For example, you refer to cell B0, or try to add a word to a number: =cat + 1 #N/A (NOT AVAILABLE) • Using VLOOKUP, for example—data needed is not available CS 105 Spring 2009

  15. Error Messages, cont. • #NUM! a problem occurs with a number =SQRT(-45) (you cannot have a square root of a negative number) • #DIV/0! when a formula divides by zero (often happens when you divide by an empty cell) CS 105 Spring 2009

  16. Testing your knowledge: • VLookUp does what? • ISTEXT returns what? • Delimiter—what’s that? CS 105 Spring 2010

More Related