week of Dec. 14: Excel Testm

 

There will be a on Tuesday covering the following topics in Excel:

--> templates
      * Be careful to save your templates on your home drive.
      * To open a file based on a template, double-click on the icon for the file.

--> linking
      * Use absolute cell references (F4) for the lookup table.
      * Open all files in the same "session" of Excel using File / Open.

--> protection

--> the IF function
      * No nested IF functions will be on the test.

--> the VLOOKUP function
      * Use absolute cell refernces (F4) for the lookup table.
      * Be prepared to translate an English description of a lookup situation into a correct table in Excel.
      * Be careful about the first value (top left corner) in your lookup table.
      * Know when to use FALSE as a fourth parameter - when you want an exact match.

VLOOKUP online video demos

In addition to the video demo of VLOOKUP on our website, here are a few more animated VLOOKUP tutorials


• YouTube has several excellent examples
  
• LondondLearning.com - Enjoy that British accent!
  
• wmich.edu - simple but illustrates how VLOOKUP works, at least in part
  
  

the key to understanding VLOOKUP

If you feel unsure about the VLOOKUP function, the safest approach is to keep in mind how it works. Try to picture all this in your mind. It may sound complicated in words, but the idea is not difficult.

Imagine you are converting a student percentage mark in G4 to a letter grade.
e.g. =VLOOKUP(G4, H1:J5, 2)
The first two parameters of the function simply tell Excel (1) where the value is that is going to be compared to the lookup table, then (2) where the lookup table is


• Excel goes to the TOP ROW of the FIRST COLUMN of the table (H1).
  
  
• It checks to see whether G4is GREATER than the value in H1.
  
  
• If G4 is greater than the value in H1, Excel moves DOWN to the next row of the table, that is, to H2.
  
  
• Again it checks see whether G4is GREATER than the value in H@.
  
  
• It continues doing this checking and moving down the table until it finds the value in the first row of the table that is greater than G4.
  
  
• When G4 is greater than the value in the table, it moves back UP one row in the table.
  
  
• It "returns" whatever it finds in the SECOND COLUMN of the table.
  

NOTE: When you use the fourth parameter, "FALSE," Excel only looks for an exaact match

Excel assignment - the IF function

Here is another simple exercise to help boost your mark, this time using the IF function in Excel.

Create a self-named file (e.g. BarackO_IFassignment.xls) and copy it to the Dropoff Folder when you are finished.

Create the file as shown for A1:B2 so that when A1 has a value higher than 36.8, B1 and B2 show the messages illustrated. If A1 has a value equal to or lower than 36.8, both B1 and B2 show the message illustrated in the green box.