1 / 14

Excel Conditional Functions

Excel Conditional Functions. 1 April 2014. COUNTIF in the real world. How many brunettes are in class? How many students wear glasses?. In Excel…. Need to define two things range – collection of cells that we’re checking against our criteria range example: I2:I9

rhian
Download Presentation

Excel Conditional Functions

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. 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

More Related