1 / 19

Computer Science & Engineering 2111

Computer Science & Engineering 2111. Lecture 4 COUNTIF(s)/SUMIF(s)/SUMPRODUCT/ AVERAGEIF(s)/LARGE/SMALL/RANK. COUNTIF FUNCTION. Counts the number of items in a range that meet a specific criteria =COUNTIF(range, criteria) Range: A continuous range Criteria: Determines what cells to count.

donnan
Télécharger la présentation

Computer Science & Engineering 2111

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. Computer Science & Engineering 2111 Lecture 4 COUNTIF(s)/SUMIF(s)/SUMPRODUCT/ AVERAGEIF(s)/LARGE/SMALL/RANK CSE 2111 Lecture 4-Countif Sumif etc.

  2. COUNTIF FUNCTION Counts the number of items in a range that meet a specific criteria =COUNTIF(range, criteria) • Range: A continuous range • Criteria: Determines what cells to count CSE 2111 Lecture 4-Countif Sumif etc.

  3. CSE 2111 Lecture 4-Countif Sumif etc.

  4. CSE 2111 Lecture 4-Countif Sumif etc.

  5. CSE 2111 Lecture 4-Countif Sumif etc.

  6. COUNTIFS FUNCTION Counts the number of items in a range (using multiple criteria and multiple ranges) that meet a specific criteria • All criterion must be true in order for the cell to be counted =COUNTIFS(criteria_range1, criteria1,[criteria_range2,criteria2], …) • Criteria_range1: A continuous range • Criteria1: Determines what cells to count • Syntax rules the same as COUNTIF CSE 2111 Lecture 4-Countif Sumif etc.

  7. CSE 2111 Lecture 4-Countif Sumif etc.

  8. SUMIF FUNCTION Sums the number of items in a range that meet a specific criteria =SUMIF(criteria_range, criteria,[sum_range]) • Criteria_Range: A continuous range • Criteria: Determines what cells to count • Sum_Range: If criteria is met, the computer will sum the corresponding entry in this range • Same criteria syntax as COUNTIF • If a sum_range argument is not used, the sum_range will be the same as the criteria_range CSE 2111 Lecture 4-Countif Sumif etc.

  9. CSE 2111 Lecture 4-Countif Sumif etc.

  10. CSE 2111 Lecture 4-Countif Sumif etc.

  11. SUMIFS FUNCTION Sums a range (using multiple criteria and multiple ranges) that meet a specific criteria • All criterion must be true in order for the cell to be summed =SUMIFS(sum_range, criteria_range1,criteria1[criteria_range2,criteria2], …) • Sum_Range: Range to sum if criterion are met • Criteria_Range1: Range of first criteria • Criteria1: Criteria for range1 • Criteria syntax rules the same as COUNTIF CSE 2111 Lecture 4-Countif Sumif etc.

  12. CSE 2111 Lecture 4-Countif Sumif etc.

  13. AVERAGEIF and AVERAGEIFS FUNCTION =AVERAGEIF(criteria_range, criteria,[average_range]) Averages the number of items in a range that meet a specific criteria =AVERAGEIFS(average_range, criteria_range1,criteria1[criteria_range2,criteria2], …) Averages a range (using multiple criteria and multiple ranges) that meet a specific criteria • All criterion must be true in order for the cell to be averaged. CSE 2111 Lecture 4-Countif Sumif etc.

  14. SUMPRODUCT FUNCTION Multiplies corresponding components in the given arrays, and returns the sum of those products. =SUMPRODUCT(array1, [array2], [array3], …) • array1: The first array argument whose components you want to multiply and then add • The array arguments must be the same dimension • Treats non-numeric cells as 0 CSE 2111 Lecture 4-Countif Sumif etc.

  15. CSE 2111 Lecture 4-Countif Sumif etc.

  16. LARGE and SMALL FUNCTION =LARGE(array, k) Returns the k-th largest value in a range • Array: A continuous range • k: The position (from the largest) in the array (range) =SMALL(array, k) Returns the k-thsmallest value in a range • Array: A continuous range • k: The position (from the smallest) in the array (range) CSE 2111 Lecture 4-Countif Sumif etc.

  17. CSE 2111 Lecture 4-Countif Sumif etc.

  18. RANK.EQ FUNCTION Returns the rank of a number in a list of numbers =RANK.EQ(number, ref, [order]) • Number: The number whose rank you want to find • Ref: The Array or range of values to rank • Order: A number specifying how to rank the numbers • 0 or omitted: Ranks in descending order • 1: Ranks in ascending order CSE 2111 Lecture 4-Countif Sumif etc.

  19. CSE 2111 Lecture 4-Countif Sumif etc.

More Related