160 likes | 174 Views
Relative, Absolute, & Mixed References. Relative Reference (Address). In C3, =A1+B2 means Display sum of the content of cell which is 2 columns to the left and 2 rows above and the content of cell which is 1 column to the left and 1 row above.
E N D
Relative Reference (Address) • In C3, =A1+B2 means Display sum of the content of cell which is 2 columns to the left and 2 rows above and the content of cell which is 1 column to the left and 1 row above. • When this formula is copied to other cells, the same instruction is copied. • E.g., if the formula is copied to D4, it becomes =B2+C3.
Absolute Reference (Address) • In C3, =$A$1+B2 means Display the sum of the content of cell which is at A1 and the content of cell which is 1 column to the left and 1 row above. • When this formula is copied to other cells, the same instruction is copied. • E.g., if the formula is copied to D4, it becomes =$A$1+C3.
Mixed Reference (Address) • In C2, =$A$1+$B2 means Add the content of cell which is at A1 and the content of cell which is in column B and in the same row. • When this formula is copied to other cells, the same instruction is copied. • E.g., if the formula is copied to C4, it becomes =$A$1+$B4.
Mixed Reference (cont.) • In B3, =$A$1+B$2 means Add the content of cell which is at A1 and the content of cell which is in the same column and in row 2. • When this formula is copied to other cells, the same instruction is copied. • E.g., if the formula is copied to C4, it becomes =$A$1+C$2.
IF() Function =IF(B2>=70,”Pass”,”Fail”)
IF() Function • Form • =IF(condition, value-for-TRUE-case, value-for-FALSE-case) • Example • Assume: B2 contains semester average • Then, in C2, we can have:=IF(B2>=70, “Pass”, “Fail”)
VLOOKUP() Function • Suppose letter grades for exam scores are assigned as follows: A – 90 or above B – 80 or above, but less than 90 C – 70 or above, but less than 80 D – 60 or above, but less than 70 F – less than 60 • Use VLOOKUP() function to assigning letter grade to a score, buy looking up a table.
VLOOKUP() • Format • =VLOOKUP( Value to look up, The range of the table, The column number containing the grade) • For example, • In the preceding case=VLOOKUP(B2, $G$7:$H$11,2)
With VLOOKUP(), Remember… • In the VLOOKUP(), the 2nd argument, the range for the lookup table, should be in absolute address. • In the lookup table, values to be looked up should be in ascending order (from small to larger).
Your Turn • Given the preceding table, look up the Tax Rate for Erickson with the VLOOK() function.
HLOOKUP() • Format • =HLOOKUP( Value to look up, The range of the table, The row number containing the grade) • For example, • In the preceding case=HLOOKUP(B2, $B$(:$F$10,2)
With HLOOKUP(), Remember… • In the HLOOKUP(), the 2nd argument, the range for the lookup table, should be in absolute address. • In the lookup table, values to be looked up should be in ascending order (from small to larger) from left to right.