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 • criteria – logical test for our range • Criteria example: “>=60”
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
Excel Functions • Three operations • COUNT, SUM, AVERAGE • Two formats • Single criterion • Multiple criteria (may be one)
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, …
COUNTIF example • How many courses is each student passing? • How many courses did at least one student pass?
SUMIF(S) and AVERAGEIF(S) • Bit more complex because they allow you to check one range for criteria and sum or average another
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
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?
SUMIFS • Parameters • sum_range, range, criteria, range, criteria • Function • Adds the value if all criteria in that position are true
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
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