1 / 21

A Capstone Chapter

A Capstone Chapter. (Review, New Functions, Nested IFs). 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

clayton-browning
Download Presentation

A Capstone Chapter

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

  14. 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 “”

  15. 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

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

  17. 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")))

  18. 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

  19. 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

More Related