Efficient Data Analysis in Excel: Techniques, Functions, and Commands

1. A Capstone Chapter (Review, New Functions, Nested IFs)

2. Objectives • Discuss several techniques used to analyze data within Excel • Use the AND and OR functions to select records within a list • Distinguish between the COUNT, COUNTA, and COUNTIF functions • Use the CONCATENATE and TRIM functions to manipulate text

3. Objectives (continued) • Explain how the Goal Seek command facilitates decision-making • Use the RANK and QUARTILE functions to order the records in a list • Use the MOD and ROW functions with conditional formatting to shade alternate rows in a spreadsheet

4. Objectives (continued) • Explore the advanced features in pivot tables and pivot charts • Customize a chart by formatting the data series, chart background, and/or using the Drawing toolbar • Create a documentation worksheet

5. Drowning in Data • Lack of data is rarely a problem in most organizations • In many cases, we have too much data • Analyzing data gives it meaning • Meaningful data becomes information used to make decisions

6. Data Analysis Techniques • Sorting – arrange lists by one or more fields • Easy to find an individual record • Calculating – manipulating data in an individual record • For example, if you know someone’s date of birth, you can calculate his/her age • Summarizing – displays records within groups and calculating • Pivot table an effective way to summarize

7. Data Analysis Techniques, cont. • Filtering – displays only records that meet specific criteria • Isolate and focus on a subset of data • Formatting – calls attention to a cell by the way it is displayed • Conditional formatting applies formatting based on cell value • Charting – graphic representation of data • Visually display results of data analysis

8. Summarizing Functions • COUNT – returns number of cells containing numeric values within a designated range • COUNTA – returns number of non-empty cells within a designated range • COUNTIF – returns number of cells meeting specific criteria with a designated range • Range • Range of cells from which you want to count cells • Criteria    • Criteria in the form of a number, expression, or text

9. Lookup Functions • HLOOKUP – looks up value in first row of table and returns entry corresponding to offset • Requires three arguments • Value being looked up • Table being searched • Number of rows to offset • VLOOKUP – looks up value in first column of table and returns entry corresponding to offset • Requires same three arguments as HLOOKUP

10. Ranking Functions • RANK – returns rank of a number in a list • Requires two arguments • Number • Name of list • QUARTILE – returns maximum value of specified quartile • Requires two arguments • Name of array • Quartile

11. Quartiles • QUARTILE(array,quart) • Array is the array or cell range of numeric values for which you want the quartile value • Quart indicates which value to return • If quart equals • 0 Minimum value • 1First quartile (25th percentile) • 2 Median value (50th percentile) • 3 Third quartile (75th percentile) • 4 Maximum value

12. Modulo Arithmetic • MOD function returns remainder after division • The result has the same sign as divisor • Requires two arguments • MOD(number,divisor) • Number -  is the number for which you want to find the remainder • Divisor -  is the number by which you want to divide number

13. Row Function • Returns the row number of a reference • ROW(reference) • Reference is the cell or range of cells for which you want the row number • If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears •  =ROW() Row in which the formula appears =ROW(C10) Row of the reference

15. Text Functions • Names in a list may not be in a preferred form • Always keep data in its smallest units • May want to merge or rearrange text • To join several text strings into one • concatenate(text1,text2,..) • =A1&B1 (not the AND function) • Can enter commas or spaces with “”

16. Text Functions • Exact(text1,text2) • Checks whether two text strings are exactly the same and returns true or false • Case sensitive • Left(text,num_char) and right(text,num_character) • Returns specific number of characters from start (or end) of text string • Len(text) • Returns the number of characters in string

17. Text • Trim(text) • Removes all spaces from text except for single spaces between words

19. Nested If Functions • Need code to summarize S,M, L and Growth and Blended funds • Use Nested if • =IF(F7="Growth",IF(G7="Large",1,IF(G7="mid cap",2,3)),IF(G7="Large",4,IF(G7="mid cap",5,6))) • Need to determine Quartiles for returns • =IF(D7<=$B$2,"First Quartile",IF(D7>$B$4,"Fourth Quartile",IF(AND(D7>$B$2,D7<=$B$3),"Second Quartile","Third Quartile")))

20. A Pivot Table • Divides records into list • Computes summary statistics for categories • Group and Show Detail command aggregates • Drag fields around pivot table to create new views • Pivot chart displays pivot table graphically

21. More on Pivot Tables • Pivot Tables can have two row fields • Objective of fund • Size of fund • One column field (or more) • Fees • One page field • Quartile • Calculate Average of Return