Ch3ClassNotes_Part2_sp11

# Ch3ClassNotes_Part2_sp11 - Chapter 3: Excel Functions, Part...

This preview shows pages 1–3. Sign up to view the full content.

Chapter 3: Excel Functions, Part 2 (of 4): Chapter Skills for Chapter 3, Part 2 Use functions that allow the user to specify criteria for including data in the aggregate operation. (These are not logical functions, but use logical comparisons to do their operations.) 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 true value occurring in ANY ONE of multiple conditions tested. o Use nested IF functions to test for a true value in ALL of the multiple conditions tested. Use the OR function to test for a true value occurring in ANY ONE of multiple conditions tested. Use the AND function to test for a true 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 Chapter Demonstration 7: Using the SUMIF and COUNTIF Functions (These are actually statistical functions.) 1. Download from Bb the practice file Employees_ch3.xlsx and save it to your machine. 2. Into any blank cell (be sure to leave a blank cell between the list and the formula cells), enter a formula that calculates the total salaries for the sales department. Use the SUMIF function: =SUMIF(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. =SUMIF(I2:I11, ">50000") (In this case, sum range and criteria range are the same.)

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
b. Sum salaries that are over the amount in an assumption cell. Use 50000 as the first value in the assumption cell. 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.
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 11/21/2011 for the course ACIS 2504 taught by Professor Mtgriffin during the Spring '08 term at Virginia Tech.

### Page1 / 5

Ch3ClassNotes_Part2_sp11 - Chapter 3: Excel Functions, Part...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online