 Download Download Presentation Excel Conditional Functions

# Excel Conditional Functions

Download Presentation ## Excel Conditional Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
##### Presentation Transcript

1. Excel Conditional Functions 1 April 2014

2. COUNTIF in the real world • How many brunettes are in class? • How many students wear glasses?

3. In Excel… • Need to define two things • range – collection of cells that we’re checking against our criteria • range example: I2:I9 • criteria – logical test for our range • Criteria example: “>=60”

4. Forms for Criteria • “>=78” • logical operator and constant. Must be in “ “ • 67 • Simple constant will test for = • F4 • Cell will test for = • “<“&F4 • Need to build the criterion for other operators and cell

5. Excel Functions • Three operations • COUNT, SUM, AVERAGE • Two formats • Single criterion • Multiple criteria (may be one)

6. COUNTIF and COUNTIFS • COUNTIF • One range, one criterion • COUNTIFS • Pairs of ranges and criteria • Use for multiple criteria by repeating range • Intended use for different ranges: • Age is greater than 50 and gender is F • Checks all first items and counts if all are true, …

7. COUNTIF example • How many courses is each student passing? • How many courses did at least one student pass?

8. SUMIF(S) and AVERAGEIF(S) • Bit more complex because they allow you to check one range for criteria and sum or average another

9. SUMIF in Excel • SUMIF (range, criteria, sum_range) • This function will add things up, depending on the criteria given. • range – a range of cells • criteria – the criteria we’re checking the range against • Same rules as COUNTIF • sum_range – the range of cells to add up • can be same or different

10. SUMIF example • Odd/Even • Note that can not use function directly • Fruits and Veggies • How much would it cost to buy one of every kind of vegetable? • How much would it cost to buy one of every kind of fruit?

11. SUMIFS • Parameters • sum_range, range, criteria, range, criteria • Function • Adds the value if all criteria in that position are true

12. Examples • Buy the grocery item if it is a fruit and if it costs less than \$2.50. • What is the total cost? • Note that you need the additional column or SUMIFS • Label the grocery items as inexpensive (<\$1), medium (\$1-\$2), or expensive (>\$2) • What is the total cost of all expensive items? • Note that there are 2 ways to do it • Shorthand when both ranges the same

13. And One JavaScript Item

14. Global Variables • Everything in a function lives only for one call • Sometimes want to keep a value around • GLOBAL variable • Place it OUTSIDE the function • Stays around until page is refreshed