RELATIVE VS.

ABSOLUTE CELL REFERENCES LEFT()/RIGHT()/MID() FUNCTIONS

There are three types of cell references used in Excel Formulas. The LEFT(), RIGHT() and MID() functions are text based functions. Each function
allows you to pull several characters from a cell based on position and how
RELATIVE = A1
many characters are needed.
ABSOLUTE = $A$1
Master Excel Advanced MIXED = A$1 or $A1

Functions/Formulas =IF(F5>=$I$2,"YES", "NO")

________________________________________________________________ By default, Excel uses a relative reference. When a formula containing relative
• SUPPLIER = LEFT 3 Characters (ACM)
cell references is copied to a new location, the relative reference will change
based on the new location.
IF() FUNCTION =LEFT(A2,3)
The IF function is one of the most popular functions in Excel. It allows you to If a formula containing absolute references is copied to a new location, the
• PART # = MIDDLE 3 Characters (110)
make logical comparisons between values and return one of two results based absolute reference will not change.
on the outcome. =MID(A2,4,3)
SHORTCUT KEY
=IF(LOGICAL_TEST, TRUE, FALSE) • Highlight the cell reference in the formula. • PRODUCT CODE = RIGHT 2 Characters (WW)
• Press function key F4
=RIGHT(A2,2)
VLOOKUP() FUNCTION SUMIF() FUNCTION

=VLOOKUP(LOOKUP_VALUE, TABLE_RANGE, =SUMIF(RANGE, CRITERIA, [SUM_RANGE])

COL_INDEX, [RANGE_LOOKUP])
The SUMIF function can be used to SUM a range of cells in a table based on
The VLOOKUP function allows you to find values from a table or range by the criteria. For example, you only want to SUM the SALES column if the STORE #
=IF(F5>=I2,"YES", "NO") row. For example, you have a list of Employee IDs and need to find the Last column is equal to 3000.
Name associated with each ID.
In the example above, we are checking if cell F5 ($36,245) is greater than or =SUMIF(B3:B272, 3000, D3:D272)
equal to cell I2 ($34,000). If TRUE, then we return YES. If FALSE, we return NO. =VLOOKUP(B2,$A$5:$I$41,2,FALSE)
NESTING FUNCTIONS
“Nesting” refers to joining multiple functions together in one formula.

=IF(AND(TEST, TEST, TEST), TRUE, FALSE)

Nesting is used to perform additional functions within a primary function. For

example, in the above formula we start with an IF() function, but we need to
test for multiple conditions. A standard IF() function only allows for one test to
be performed. By nesting an AND() function we can perform multiple test to
fulfill the requirements. INDEX()/MATCH() FUNCTIONS
=IF(AND(MIN(B5:E5)>=8000, H5="YES"), "BONUS", "NO BONUS") The VLOOKUP() function is great but has some limitations. For example, the
VLOOKUP can only look up a value from left to right. In order to overcome this
limitation you can use the INDEX()/MATCH() function pair. By nesting the
MATCH function within the INDEX() function you can look left to right or right to
left for the matching value.

Master Excel Advanced Functions/Formulas

[Link]