Create Presentation
Download Presentation

Download Presentation
## Excel Conditional Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

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