This preview has intentionally blurred sections. Sign up to view the full version.
View Full DocumentThis preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: Chapter 3: Excel Functions, Part 2 (of 4) Chapter Skills for Chapter 3, Part 2 • Use some statistical functions that allow the user to specify criteria for including data in the operation. o SUMIF (or SUMIFS) functions to sum numeric data that meets some criteria. o COUNTIF (or COUNTIFS) functions to count cells that meet some criteria. • Use the IF function to structure a simple decision within a formula. • Use IF functions that are nested in other functions (or vice versa) such that the inner function returns an argument value to the outer function. (Can be applied to the previous two items above.) o Use nested IF functions to test for a t rue value occurring in ANY ONE of multiple conditions tested. o Use nested IF functions to test for a t rue value in ALL of the multiple conditions tested. • Use the OR function to test for a t rue value occurring in ANY ONE of multiple conditions tested. • Use the AND function to test for a t rue value occurring in ALL of the multiple conditions tested. • Use assumption cells for formula inputs. • Use named ranges for formula inputs. Chapter Vocabulary for Chapter 3, Part 2 • SUMIF and COUNTIF functions • IF function (know and understand its arguments) • Nesting functions • Logical functions • Boolean value • OR function • AND function • OR logic • AND logic 1 Chapter Demonstration 7: Using the SUM I F and COUNT I F Functions (These are actually statistical functions.) 1. Download from Bb the practice file Employees_ch3.xlsx and save it to your machine. 2. I nto any blank cell, enter a formula that calculates the total salaries for the sales department. Use the SUMIF function: =SUM I F(G2:G21,"Sales",I2:I21) Experiment with changing “Sales” to another department name to see how it affects the results. 3. Sum a range using numeric criteria. a. Sum all salaries that are over $50,000. =SUM I F( I2:I11, ">50000") (In this case, sum range and criteria range are the same.) b. Sum salaries that are over the amount in an assumption cell. Use 50000 as the first value in the assumption cell. =SUM I F( I2:I21, ">" & K2) ( You will want to make these range references absolute if you wish to copy the formula to a new location.) 4. Sum salaries that are in the sales department AND are over $50,000. (Apply criteria to two different columns.) Both criteria must be met for values to be included in the sum. =SUM I FS(I2:I21,G2:G21,"Sales",I2:I21,">"&K2) 5. Count values in a cell range that match some criteria. a. Count the number of employees who work in the Sales department. =COUNT I F(G2:G21,"Sales") Note that the criteria range is also the count range since no range must be numeric. (Can only sum numeric ranges, but can count any type of cell content.) Chapter Demonstration 8: Use a single I F Function 2 The company has a retirement fund in which it contributes funds on behalf of each employee. The employee is vested in the company contributions after 5 years of employee....
View
Full Document
 Spring '08
 MTGriffin
 Sociology, Logic, Nested function

Click to edit the document details