Excel Assignment
maladam
L_02-2PowerfulExcelFunctions2.pptx1
Using Excel for Data Analysis
Harnessing the Power of Excel's Built-In Functions
1
Absolute vs Relative Addresses
The power of Excel is centered on the ability to write a formula once and copy
Download and open L_3-1 ExcelFunctions.xlsx
2
What's the difference between:
=B2 / G1
And
=B2 / $G$1
Conditional Values: IF
=IF ( condition, T, F)
3
Conditional Values: IF
4
Complex IF
=IF (condition, T, IF(Condition,T,F))
Conditionals: sumIF() countIF()
5
=countif(range, criteria)
=sumif(range, criteria, sum_range)
Data - Lookups
6
=vlookup(what,where,column,type)
Looks up a value and returns corresponding value from table (same row)
Data - Lookups
7
=match(what,where,type)
Looks up a value from a single column / row of data and returns the position (as an integer) of the location where the first match is found
Text Functions
8
Explore several of Excel's powerful functions to manipulate text data
In Class Exercise
Test your understanding of:
Absolute/Relative Addresses – Named Ranges
IF()
COUNT()
SUMIF()
COUNTIF()
VLOOKUP()
MATCH()
Text Manipulations
by working through remaining tabs of workbook!
9
