Popular in Course
verified elite notetaker
Popular in Department
This 3 page Class Notes was uploaded by nana Notetaker on Friday August 28, 2015. The Class Notes belongs to a course at a university taught by a professor in Fall. Since its upload, it has received 12 views.
Reviews for notes
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 08/28/15
CIS 150 Appendix B Advanced Filters Database and Summary Advanced FiItE Advanced Filters displays a subset of the rows in an Excel table or a range of data that match the criteria you specify 0 Advanced Filters enable you to Perform OR conditions across multiple elds To create complex criteria using functions and formulas Criteria Range the lter criteria in a separate range gt Understanding the Criteria Range 0 Criteria Range an area in a worksheet separate from a range of data or an Excel table used to specify the criteria for the data to be displayed after the lter is applied to the range or the Excel Table 0 The criteria range consists of A header row that lists eld names from the table s header row At least one row with the speci c ltering criteria for each eld 0 The criteria range speci es which records from the data range will be included in the ltered data 0 Criteria placed on the same row are considered to be connected with the logical operator AND That means that all criteria in the same row must be met before a record is included in the ltered data 0 Criteria placed on separate rows of the criteria range are treated as being connected by the logical operator OR That means that meet all the criteria on either row in the criteria range will be displayed 0 To specify criteria between a range of values in the same eld use to same led name repeated in separate cells within the same row to match a range of values BETWEEN gt Creating a Criteria Range 0 Criteria Range can be placed above the data or on a different worksheet if you need to use several criteria ranges in different cells to perform calculations based on various sets of ltered records Never place next to it as it can get hidden by advanced lters Create Criteria Range Select the range you want to place your criteria Copy Field Names Insert conditions 0K 2 Using Advanced Criteria Range Select cell Click Data tab Sort amp Filter Advanced Filter the list inpace OK gt Using Database Functions to Summarize Data 0 Database Functions DFwnctions perform summary data analysis such as sum average and count on an Excel table or data range based on criteria speci ed in a criteria range Function Description DAVERAGE Returns the average of the values that meet speci ed criteria DCOUNT Returns the number of cells containing numbers that met speci ed criteria DCOUNTA Returns the number of nonblank cells that meet speci ed criteria DMAC Returns the maximum value in the search column that meets speci ed criteria DMIN Returns the minimum value in the search column that meets speci ed criteria DSTDEV Returns the estimate of standard deviation based on a sample of entries that meet the speci ed criteria DSUM Returns the sum of the values in the summary column that meet speci ed criteria In a Database Functions the criteria range is used as one of the arguments of the function DatabaseFunctionName Table Range Column to Summarize Criteria Range 0 Table Range refers to the cells where the data to summarize is located including the column header Column to Summarize is the column name of the eld to summarize entered within quotation marks and Criteria Range is the range where the criteria that determine which records are used in the calculation are speci ed Entering Database Functions Select cell Click Insert Function Or Select a Category arrow Database DAVERAGE OK appropriate arguments in the boxes I OK gt Summarizing Data Using the COUNTIFS SUMIFS and AVERAGEIFS Functions COUNTIFS SUMIFS and AVERAGEIFS former twnctions enables you to summarize the data using several conditions similar to the latter functions COUNTIF SUMIF and AVERAGEIF but they only use one condition gt COUNTIFS Function COUNTIFS Function counts the number of cells within a range that meet multiple criteria To count the number of cells within up to a 127 ranges and a 127 criteria COUNTIFS Criteriarange1 Criterial Criteriarange2 Criteria2 Criteriarange1 amp Criteriarange2 columns of data in which to evaluate the associated criteria and Criterial amp Criteria2 represent in the form of a number an expression a cell reference or text that de ne which cells will be counted Each cell in a range is counted only if all of the corresponding criteria speci ed in the COUNTIFS function are true CONTIFS Function Select ce Click Insert Function Or Selecta Category arrow l Statistical l COUNTIFS GM appropriate arguments in boxes OK gt SUMIFS Function SUMIFS Function adds values in a range that meet multiple criteria using the syntax To count the number of cells within up to a 127 ranges and a 127 criteria SUMIFS Sumrange Criteriarange1 Criterial Criteriarange2 Criteria2 Sumrange is the range you want to add Criteriarange1 amp Criteriarange2 columns of data in which to evaluate the associated criteria and Criterial amp Criteria2 represent in the form of a number an expression a cell reference or text that de ne which cells will be counted SUMIFS Function Select ce Click Insert Function Or Selecta Categoryarrow Math amp Trig l SUMIFS l 0K appropriate arguments in boxes OK gt AVERAGEIFS Function AVERAGEIFS Function calculates the average of values within a range of cells that meet multiple conditions The number of cells within up to a 127 ranges and a 127 criteria AVERAGEIFS Averagerange Criteriarange1 Criterial Criteriarange2 Criteria2 Averagerange is the range to average Criteriarange1 amp Criteriarange2 columns of data in which to evaluate the associated criteria and Criterial amp Criteria2 represent in the form of a number an expression a cell reference or text that de ne which cells will be counted AVERAGEIFS Function Select cell Click Insert Function Or Select a Categoryarrow l Statistical l AVERAGEIFS l appropriate arguments in boxes OK
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'