Error Messages, VLookup, Practical Tips

1
Error Messages, VLookup, Practical Tips
Explain error messages What use is
VLookup? How do you import text into Excel?
p. 157 Course Guide
2
Error Messages (sometimes change with versions of
Excel)

• Column not wide enough
• 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 exampledata needed is not
  available

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

4
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

5
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,AC2AE14,3)

6
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.
7
VLOOKUP(C18,E13F17,2)
Col.2 of LOOKUP table
Cell to evaluate
Lookup Table is E13F17
Use vlookup.xls spreadsheet
8
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

9
Concatenating -- Using
Using the wizard to enter a function

• As in MySQL, you can add a space between each
  word.

10
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

11
Providing User Feedback

• Some functions are built into Excel to help
  provide feedback and test content
• They could be helpful to you

12
Examples of useful functions

• ISERROR(B2) equals TRUE
• ISNUMBER(B3) equals FALSE
• ISTEXT(B4) equals TRUE
• ISBLANK(B5) equals TRUE
• Formula view

13
How to use these functions

• You want to calculate the average of the range
  C1C4, but you can't be sure that the cells
  contain numbers.
• The formula AVERAGE(C1C4) returns DIV/0!
• if C1C4 does not contain any numbers. Use
• IF(ISERROR(AVERAGE(C1C4)),"No
  Numbers",AVERAGE(C1C4))

14
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

15
To import a text file
16
(No Transcript)
17
Testing your knowledge

• NAME?
• ISTEXT
• .CSV
• Delimiter