1 / 110

Microsoft  Excel 

Microsoft  Excel . Logical Functions Mathematical Functions Statistical Functions Lookup Functions. Microsoft  Excel  Logical Functions. AND =AND(logical1, [logical2], ...) OR =OR(logical1, [logical2], ...) NOT =NOT(logical) IF

brad
Download Presentation

Microsoft  Excel 

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. Microsoft Excel  • Logical Functions • Mathematical Functions • Statistical Functions • Lookup Functions

  2. Microsoft Excel  Logical Functions • AND • =AND(logical1, [logical2], ...) • OR • =OR(logical1, [logical2], ...) • NOT • =NOT(logical) • IF • =IF(logical_test, [value_if_true], [value_if_false]) • NESTEDIF • IF( condition1, value_if_true, IF( condition2, value_if_true, value_if_false)) • IFERROR • =IFERROR(value, value_if_error)

  3. The AND function • Syntax: • =AND(logical1, [logical2], ...) • Arguments: • logical1Required • The first condition that you want to test that can evaluate to either TRUE or FALSE. • logical2, ... Optional • Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.

  4. The AND function • Description: • Returns FALSE if one or more arguments is FALSE • Otherwise, all arguments must evaluate TRUE • Remarks: • Arguments must evaluate to logical values • Arguments must be arrays or references that contain logical values • Text and empty cells are ignored in arrays or references • Errors: • #VALUE – If no logical values exist in a specified range

  5. The AND function – EXAMPLE 1      

  6. The AND function – EXAMPLE 2      

  7. The OR function • Syntax: • =OR(logical1, [logical2], ...) • Arguments: • logical1Required • The first condition that you want to test that can evaluate to either TRUE or FALSE. • logical2, ... Optional • Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.

  8. The OR function • Description: • Returns TRUE if one or more arguments is TRUE • Otherwise, all arguments must evaluate FALSE • Remarks: • Arguments must evaluate to logical values • Arguments must be arrays or references that contain logical values • Text and empty cells are ignored in arrays or references • Errors: • #VALUE – If no logical values exist in a specified range

  9. The OR function      

  10. The NOT function • Syntax: • =NOT(logical) • Arguments: • logical Required • A value or expression that can be evaluated to TRUE or FALSE.

  11. The NOT function • Description: • Reverses the value of its argument. • Remarks: • If logical is FALSE, NOT returns TRUE • if logical is TRUE, NOT returns FALSE • Errors: • None

  12. The NOT function

  13. The IF function • Syntax: • =IF(logical_test, [value_if_true], [value_if_false]) • Arguments: • logical_testRequired • Any value or expression that can be evaluated to TRUE or FALSE. • value_if_trueOptional • The value that you want to be returned if the logical_test argument evaluates to TRUE. • If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). • To display the word TRUE, use the logical value TRUE for the value_if_true argument.

  14. The IF function • Syntax: • =IF(logical_test, [value_if_true], [value_if_false]) • Arguments: • value_if_falseOptional • The value that you want to be returned if the logical_test argument evaluates to FALSE. • If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. • If logical_test evaluates to FALSE and the value of the value_if_false argument is omitted (that is, in the IF function, there is a comma following the value_if_true argument), the IF function returns the value 0 (zero).

  15. The IF function • Description: • The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. • Remarks: • Up to 7 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (2003) • Up to 64 IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. (2007) • If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. • Errors: • None

  16. The IF function   value_if_true [value_if_false]

  17. Nested If in Excel • http://www.fontstuff.com/excel/exltut01.htm • A nested IF statement says something like... • "If the answer is yes, do this. If the answer is no do this or this (depending on...“ • Syntax: • IF( condition1, value_if_true, IF( condition2, value_if_true, value_if_false ))

  18. Nested IF Contd.

  19. Nested IF • Example 2 (Rule 2) • If cell B1 (which contains a student’s total points out of a 100 scale) is greater than or equal to 90 then give her an A, if it is greater than equal to 80 and less than 90 then give her a B, if it is greater than equal to 70 and less than 80 then give her a C, if it is greater than equal to 60 and less than 70 then give her a D, and if it is less than 60 then give her an F • Structure the Nested IF • IF B1 >= 90 THEN “A” • ELSE IF B1 >= 80 THEN “B” • ELSE IF B1 >= 70 THEN “C” • ELSE IF B1 >= 60 THEN “D” • Else “F” • END IF

  20. Nested IF • What is the syntax used to implement this rule in MS Excel? • =IF(B1>=90,"A",IF(B1>=80,"B",IF(B1>=70,"C",IF(B1>=60,"D","F"))))

  21. The Iferrorfunction • Syntax: • =IFERROR(value, value_if_error) • Arguments: • ValueRequired. The argument that is checked for an error. • Value_if_errorRequired. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

  22. The iferrorfunction • Description: • Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula. • Remarks: • If value or value_if_error is an empty cell, IFERROR treats it as an empty string value (""). • If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value. See the second example below. • Errors • None

  23. The iferror function • Example:

  24. Microsoft ExcelMathematical Functions • SUM • =SUM(number1,[number2], ...) • SUMIF • =SUMIF(range,criteria,[sum_range]) • SUMIFS • =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) • ROUND • =ROUND(number,num_digits)

  25. The SUM function • Syntax: • =SUM(number1, [number2], [number3], [number4], ...) • Arguments: • number1Required • The first item that you want to add. • number2, number3, number4, ... Optional • The remaining items that you want to add, up to a total of 255 items.

  26. The SUM function • Description: • Adds all the numbers that you specify as arguments. • Remarks: • Each argument can be a range, a cell reference, an array, a constant, a formula, or the result from another function. • If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, or text in the array or reference are ignored. • Errors: • If any arguments are error values, or if any arguments are text that cannot be translated into numbers, Excel displays an error.

  27. The SUM function

  28. The SUMIF function • Syntax: • =SUMIF(range, criteria, [sum_range]) • Arguments: • rangeRequired • The range of cells that you want evaluated by criteria. • Cells in each range must be numbers or names, arrays, or references that contain numbers. • Blank and text values are ignored. • criteriaRequired • The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. • Criteria can be expressed as 32, ">32", B5, "32", "apples", or TODAY(). • sum_range Optional • The actual cells to add, if you want to add cells other than those specified in the range argument. • Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

  29. The SUMIF function • Description: • Sums the values in a range that meet criteria that you specify. • Remarks: • See the Microsoft® Excel® help for additional remarks. • Errors: • None

  30. The SUMIF function

  31. The SUMIFsfunction • Syntax: • SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) • Arguments: • sum_rangeRequired. One or more cells to sum, including numbers or names, ranges, or cell references that contain numbers. Blank and text values are ignored. • criteria_range1Required. The first range in which to evaluate the associated criteria. • criteria1Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32." • criteria_range2, criteria2, …Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

  32. The SUMIFs function • Description: • Adds the cells in a range that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula: • =SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10") IMPORTANT   The order of arguments differ between the SUMIFS and SUMIF functions. In particular, thesum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order

  33. The SUMIFs function • Remarks: • Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two criteria_range arguments. If the first cell ofcriteria_range1 meets criteria1, and the first cell of criteria_range2 meets critera2, the first cell ofsum_range is added to the sum, and so on, for the remaining cells in the specified ranges. • Cells in the sum_range argument that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0 (zero). • Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_rangeargument must contain the same number of rows and columns as the sum_range argument. • You can use the wildcard characters — the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. • Errors: • None

  34. The SUMIFs function

  35. The ROUND function • Syntax: • =ROUND(number, num_digits) • Arguments: • numberRequired • The number that you want to round. • num_digits Required • The number of digits to which you want to round the number argument.

  36. The ROUND function • Description: • Rounds a number to a specified number of digits. • Remarks: • If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places. • If num_digits is 0, the number is rounded to the nearest integer. • If num_digits is less than 0, the number is rounded to the left of the decimal point. • Errors: • None

  37. The ROUND function =ROUND(-1.475,2) Rounds -1.475 to two decimal places

  38. Microsoft ExcelStatistical Functions • AVERAGE • =AVERAGE(number1, [number2],...) • AVERAGEIF • =AVERAGEIF(range, criteria, [average_range]) • COUNT • =COUNT(value1, [value2],...) • COUNTIF • =AVERAGEIF(range, criteria, [average_range])COUNTIFS • COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) • COUNTA • =COUNTA(value1, [value2],...) • MAX • =MAX(number1,[number2],...) • MIN • =MIN(number1,[number2],...) • LARGE • =LARGE(array,k) • SMALL • =LARGE(array,k)

  39. The AVERAGE function • Syntax: • =AVERAGE(number1, [number2],...) • Arguments: • number1Required • The first number, cell reference, or range for which you want the average. • number2, ... Optional • Additional numbers, cell references or ranges for which you want the average, up to a maximum of 255.

  40. The AVERAGE function • Description: • Returns the average (arithmetic mean) of the arguments. • Remarks: • Arguments can either be numbers or names, ranges, or cell references that contain numbers. • Logical values and text representations of numbers that you type directly into the list of arguments are counted. • If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included. • Errors: • Arguments that are error values or text that cannot be translated into numbers cause errors.

  41. The AVERAGE function

  42. The AVERAGEIF function • Syntax: • =AVERAGEIF(range, criteria, [average_range]) • Arguments: • rangeRequired • One or more cells to average, including numbers or names, arrays, or references that contain numbers. • criteriaRequired • The criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged. • average_range Optional • The actual set of cells to average.

  43. The AVERAGEIF function • Description: • Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. • Remarks: • If average_range is omitted, range is used. • Cells in range that contain TRUE or FALSE are ignored. • If a cell in average_range is an empty cell, AVERAGEIF ignores it. • If a cell in criteria is empty, AVERAGEIF treats it as a 0 value. • Errors: • #DIV/0 – If range is a blank or text value. • #DIV/0 – If no cells in the range meet the criteria.

  44. The AVERAGEIF function =AVERAGEIF(B2:B5,"<23000")

  45. The AVERAGEIF function =AVERAGEIF(B2:B5,"<23000") =14000

  46. The AVERAGEIF function =AVERAGEIF(A2:A5,"<95000")

  47. The AVERAGEIF function =AVERAGEIF(A2:A5,"<95000") =#DIV/0

  48. The AVERAGEIF function =AVERAGEIF(A2:A5,">250000",B2:B5)

  49. The AVERAGEIF function =AVERAGEIF(A2:A5,">250000",B2:B5) =24500

  50. The AVERAGEIFsfunction • Syntax: • AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) • Arguments: • Average_rangeRequired. One or more cells to average, including numbers or names, arrays, or references that contain numbers. • Criteria_range1, criteria_range2, … Criteria_range1 is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which to evaluate the associated criteria. • Criteria1, criteria2, ... Criteria1 is required, subsequent criteria are optional. 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

More Related