1 / 28

Day 9: Excel Chapter 7

Day 9: Excel Chapter 7. Cody Cutright CS 101 September 16, 2013. Math and Statistics. Do not let the words math and statistics scare you, Excel will do all of the work. You rely on statistics everyday to make routine decisions. Math and Statistical Function Categories.

kass
Download Presentation

Day 9: Excel Chapter 7

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. Day 9:Excel Chapter 7 Cody Cutright CS 101 September 16, 2013

  2. Math and Statistics • Do not let the words math and statistics scare you, Excel will do all of the work. • You rely on statistics everyday to make routine decisions.

  3. Math and Statistical Function Categories • We’ve previously seen the SUM, AVERAGE, and COUNT functions. • Now we introduce SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS, and COUNTIFS • The functions in these categories rely on a conditional statement (An IF statement) • True returns one result, false the other

  4. SUMIF • Calculates the total of a range of values when a specified condition is met. • Used in place of SUM when you only want to calculate totals for certain data.

  5. SUMIF – Cont’d Form: =SUMIF(Range,Criteria,Sum_Range) Range: The range of cells you want to evaluate to test your criteria. Criteria: The condition specified that decides whether or not Excel sums values. Sum_Range: Designates the cell values to be added if the criteria is met

  6. averageIF • Calculates the average of values in a range when a specified condition is met. • Used in place of AVERAGE when you only want to average certain data.

  7. AVERAGEIF – cont’d Form: =AVERAGEIF(Range,Criteria,Average_Range) Range: The range of cells you want to evaluate to test your criteria. Criteria: The condition specified that decides whether or not Excel averages values. Sum_Range: Designates the cell values to be averaged if the criteria is met

  8. countIF Form: =COUNTIF(Range,Criteria) Range: The range of cells you want to evaluate to test your criteria. Criteria: The condition specified that decides whether or not Excel counts values.

  9. SumIF example

  10. (SUM/Count/AVERAGE)IFS • These functions are structured backwards from the previous arguments. • These can be used to SUM/COUNT/AVERAGE

  11. SUMIFs • Calculates a total of a range of values when multiple conditions are met. • Form: =SUMIFS(sum_range,criteria1_range1,criteria1,criteria2_range2,criteria2,…)

  12. SUMIFs - cont’d • Sum_Range: Designates the cells containing values to add if the condition is met • Criteria_Range1: Specifies the first range of cells you want to evaluate to see if they meet a particular condition. • Criteria1: Specifies the condition for the criteria_range1 argument.

  13. AVERAGEIFS/COUNTIFS • Work similar to SUMIFS • Example Link:

  14. Statistical Functions RANK.EQ: Identifies the rank of a value, omitting the next rank when tie values exist. RANK.AVG: Identifies the rank of a value, providing an average ranking for identical values. Same form as the RANK function: =RANK.(EQ/AVG)(number,ref,order)

  15. Logical and Lookup Functions Logical functions enable you to test conditions to determine if they are true or false. We have dealt with logical functions before, we will now turn to nested logical functions.

  16. Nested Functions Excel allows functions to have embedded (nested) functions. Sidenote: Excel permits up to 64 IF statements in one formula.

  17. Nested IF within an IF • If a representative sells up to $1,000, the rep earns a 5% commission on the sales. • If a representative sells over $1,000 and up to $5,000, the rep earns a 7.25% commission on the sales. • If a representative sells over $5,000, the rep earns a 10% commission on the sales.

  18. Nested IF within an IF

  19. Don’t be intimidated, use a flowchart! Insert cell values Value_if_true E2*B$3 Sales * 5% IFE2 <= A$3 IF sales<= $1,000 Value_if_true E2*B$4 Sales * 7.25% IF E2 <= A$4 IF sales<= $5,000 Value_if_false E2*B$5 Sales * 10% Value_if_false

  20. How Many Logical Tests? In the previous example, we had three possible outcomes, and two logical tests. In general, for any N possible outcomes, you use N-1 logical tests.

  21. AND Function The AND function accepts two or more logical tests and displays TRUE if all conditions are true, or FALSE if any condition is not satisfied. Form: =AND(logical1,logical2) **Up to 255 conditions

  22. AND in Action The function only displays true when both conditions are met.

  23. OR Function The OR function also accepts two or more conditions and returns TRUE if any of the conditions are met, and only returns FALSE if all conditions are false. Form: =OR(logical1,logical2) *Also up to 255 logical tests

  24. OR in Action Displays TRUE if one of the conditions is satisfied.

  25. NOT Function The NOT function contains only one logical argument and returns TRUE if the argument is false and FALSE if the argument is true. Form: =NOT(logical)

  26. Data Analysis ToolPak The Analysis ToolPakis an add-in program that contains tools for performing complex statistical analysis.

  27. Enable the ToolPak • File Tab -> Options • Select Add-Ins on the left side • Make sure Excel Add-Ins is selected, then click Go • Check the Analysis ToolPak checkbox, then OK

  28. Use a tool from the ToolPak The Analysis ToolPakis an add-in program that contains tools for performing complex statistical analysis.

More Related