Download
excel conditional functions n.
Skip this Video
Loading SlideShow in 5 Seconds..
Excel Conditional Functions PowerPoint Presentation
Download Presentation
Excel Conditional Functions

Excel Conditional Functions

76 Views Download Presentation
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