1 / 16

Error Messages, VLookup, Practical Tips

Error Messages, VLookup, Practical Tips. What use is VLookup? How do you import text into Excel?. VLOOKUP, HLOOKUP 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

clark-bartlett
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 import text into Excel?

  2. VLOOKUP, HLOOKUP 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

  3. More on Vlookup • Requires three arguments: • the numeric value (or cell) to look up • the range of the table • the column number containing the value you want to return =VLOOKUP(X5,$AC$2:$AE$14,3) Note: column AD is hidden, so we say column 3 in the formula

  4. Vlookup for student with 733 points Using an approximate match, searches for the value 733 in column AC, finds the largest value less than or equal to 733 in column AC which is 720 and then returns the value from column AE in the same row.

  5. =VLOOKUP(C18,$E$13:$F$17,2) Col.2 of LOOKUP table LOCK! Cell to evaluate Lookup Table is E13:F17 Use vlookup.xls spreadsheet

  6. String Operations • Concatenation (putting two words together) • Operator is & e.g. = B3 & C4 • Concatenation can also be done by a function • e.g. =CONCATENATE(B3,C4) • Operands can be : • character strings: “Tarzan” & “Jane” • cell addresses containing text values: B3 & B4

  7. Concatenating -- Using & Using the wizard to enter a function: • As in MySQL, you can add a space between each word.

  8. Adding a space • Strings always appear in quotes in Excel. • To add a space, just put it between quotes. • Remember, there is an ASCII value for a space! • You can also put a space between the quote and the start of Water

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

  10. Examples of useful functions =ISERROR(B2) equals TRUE =ISNUMBER(B3) equals FALSE Formula view:

  11. Examples of useful functions • =ISTEXT(B4) equals TRUE • =ISBLANK(B5) equals TRUE • Formula view:

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

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

  14. To import a text file

  15. Testing your knowledge: • VLookUp does what? • ISTEXT returns what? • Delimiter—what’s that? • Your moment of Zen http://youtube.com/watch?v=K50l-9RNBo8

More Related