Download Presentation
Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management

Loading in 2 Seconds...

1 / 46

# Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management - PowerPoint PPT Presentation

Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management Chapter 2 Advanced Functions and Formulas. Advanced Functions and Formulas. Quick Links to Presentation Contents. Name Ranges Statistical Functions Math and Trigonometry Functions CHECKPOINT 1

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

## PowerPoint Slideshow about 'Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management' - dian

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Excel 2010

Level 2

Unit 1Advanced Formatting, Formulas, and Data Management

Chapter 2Advanced Functions and Formulas

Advanced Functions and Formulas

Quick Links to Presentation Contents

• Name Ranges
• Statistical Functions
• Math and Trigonometry Functions
• CHECKPOINT 1
• Manage Range Names
• Lookup Functions
• Financial Functions
• Logical Functions
• Text Functions
• CHECKPOINT 2
Name Ranges…continued

To create a range name:

• Select the desired cells.
• Click in the Name box located at the left end of the Formula bar.
• Type the desired range name.
• Press Enter.

Name box

Name Ranges…continued
• When creating a name for a cell or a range of cells, the following naming rules apply:
• Names can be a combination of letters, numbers, underscore characters, or periods, up to 255 characters.
• The first character must be a letter, an underscore, or a backslash (\).
• Spaces are not valid within a range name. Use underscore characters or periods to separate words.
• A valid cell address cannot become a range name.
• Range names are not case sensitive.
Statistical Functions
• Commonly used statistical functions include AVERAGE, MAX, and MIN, where AVERAGE returns the arithmetic mean, MAX returns the largest value, and MIN returns the smallest value in the range.
• Another function used often is COUNT, which returns the number of cells that contain numbers or dates.
• Empty cells, text labels, or error values in the range are ignored.
Statistical Functions…continued
• In a worksheet that requires cells containing text, or cells containing a combination of text and numbers (such as Model-2146) to be counted, Excel provides the COUNTA function.

Formula =COUNT(A2:A6)

returns zero.

Formula =COUNTA(A2:A6)

returns the correct result.

Statistical Functions…continued
• Use the COUNTIF function to count the number of cells within a range that meet a single criterion.
• COUNTIFS is used to count cells that meet multiple criteria.

Formula

=COUNTIF(Title,"RN")

Formula

=COUNTIFS(Title,"RN",PDCurrent,"Yes")

Statistical Functions…continued

To create a COUNTIF formula:

• Make the desired cell active.
• Click the Insert Function button in the Formula bar.
• At the Insert Function dialog box, change the category to the Statistical option.
• Select the COUNTIF option.
• Click OK.

continues on the next slide…

COUNTIF option

Statistical Functions…continued
• Enter the range address or the range name to select by in the Range text box.
• Enter the condition expression or text in the Criteria text box.
• Click OK.

Criteria text box

Statistical Functions…continued

To create a COUNTIFS formula:

• Make the desired cell active.
• Click the Insert Function button in the Formula bar.
• At the Insert Function dialog box, change the category to the Statistical option.
• Select the COUNTIFS option.
• Click OK.

continues on the next slide…

COUNTIFSoption

Statistical Functions…continued
• Enter the range address or the range name to select by in the Criteria_range1text box.
• Enter the condition expression or text in the Criteria1text box.
• Enter the range address or the range name to select by in the Criteria_range2text box.
• Enter the condition expression or text in the Criteria2text box.
• Continue adding criteria range expressions and criteria as needed.
• Click OK.

Criteria2text box

Statistical Functions…continued
• The AVERAGEIF function is used to find the arithmetic mean of the cells within the specified range that meet a single criterion.
• The AVERAGEIFS function is used to average cells that meet multiple criteria.

Formula

=AVERAGEIF(Campus,"Portland",Total)

Formula

=AVERAGEIFS(Total,Campus,"Sunnyside",Year,"<2010")

Statistical Functions…continued

To create an AVERAGEIF formula:

• Make the desired cell active.
• Click the Insert Function button in the Formula bar.
• At the Insert Function dialog box, change the category to the Statistical option.
• Select the AVERAGEIF option.
• Click OK.

continues on the next slide…

AVERAGEIFoption

Statistical Functions…continued
• Enter the range address or the range name to select by in the Range text box.
• Enter the condition expression or text in the Criteria text box.
• Enter the range address or range name to average in the Average_range text box.
• Click OK.

Average_range text box

Statistical Functions…continued

To create an AVERAGEIFS formula:

• Make the desired cell active.
• Click the Insert Function button in the Formula bar.
• At the Insert Function dialog box, change the category to the Statistical option.
• Select the AVERAGEIFS option.
• Click OK.

continues on the next slide…

AVERAGEIFSoption

Statistical Functions…continued
• Enter the range address or the range name to average in the Average_rangetext box.
• Enter the range address or the range name to select by in the Criteria_range1 text box.
• Enter the condition expression or text in the Criteria1text box.
• Enter the range address or the range name to select by in the Criteria_range2text box.
• Enter the condition expression or text in the Criteria2text box.
• Continue adding criteria range expressions and criteria as needed.
• Click OK.

Average_range text box

Math and Trigonometry Functions
• Within the math and trigonometry function category, Excel includes SUMIF to add the cells within a range that meet a single criterion and SUMIFS to add the cells within a range that meet multiple criteria.

Formula

=SUMIF(Supplier,"101",StdCost)

Formula

=SUMIFS(StdCost,Supplier,"350",MinQty,">4")

Math and Trigonometry Functions…continued

To create a SUMIF formula:

• Make the desired cell active.
• Click the Formulas tab.
• Click the Math & Trig button in the Function Library group.
• Scroll down and click the SUMIFoption.

continues on the next slide…

SUMIF option

Math and Trigonometry Functions…continued
• Enter the range address or range name to select by in the Range text box.
• Enter the condition expression or text in the Criteria text box.
• Enter the range address or range name to add in the Sum_range text box.
• Click OK.

Sum_range text box

CHECKPOINT 1
• Assigning this to a cell or a range of cells allows you to reference the source by a descriptive label.
• function
• code
• number
• name
• Use this function to count cells within a range that meet a single criterion.
• AVERAGEIF
• COUNTIF
• AVERAGEIFS
• COUNTIFS

Answer

Answer

Next Question

Next Question

• Range names can be up to this many characters.
• 255
• 155
• 100
• 25
• This function is used to average cells that meet multiple criteria.
• AVERAGEIF
• COUNTIF
• AVERAGEIFS
• COUNTIFS

Answer

Answer

Next Question

Next Slide

Manage Range Names

To edit a range name:

• Click the Formulas tab.
• Click the Name Manager button in the Defined Names group.
• Click the desired range name.
• Click the Edit button.

continues on next slide…

Edit button

Manage Range Names…continued
• Type a new range name in the Name text box.
• Click OK.
• Click Close.

Name text box

Manage Range Names…continued

To delete a range name:

• Click the Formulas tab.
• Click the Name Manager button in the Defined Names group.
• Click the desired range name.
• Click the Delete button.
• Click OK.
• Click Close.

Delete button

Lookup Functions
• The Lookup & Reference category of functions provides formulas that can be used to look up values in a range.
• Excel provides two lookup functions: VLOOKUP and HLOOKUP, which refer to a vertical or horizontal lookup, respectively.
• The structure of a VLOOKUP formula is =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
• The structure of an HLOOKUP formula is =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).
Lookup Functions…continued

Lookup table

is named grid.

VLOOKUP formula populates E4:E13 by matching the salary grid rating number in column D with the corresponding salary grid rating number in the lookup table named grid.

Formula in E4 is =VLOOKUP(Rating,grid,2).

Lookup Functions…continued

To create a VLOOKUP formula:

• Make the desired cell active.
• Click the Formulas tab.
• Click the Lookup & Reference button in the Function Library group.
• Click the VLOOKUP option.

continues on next slide…

VLOOKUP option

Lookup Functions…continued
• Enter the cell address, range name, or value in the Lookup_value text box.
• Enter the range or range name in the Table_array text box.
• Type the column number to return values from in the Col_index_num text box.
• Type FALSE or leave blank for TRUE in the Range_lookup text box.
• Click OK.

Range_lookup text box

Lookup Functions…continued
• The HLOOKUP function uses the same argument parameters as VLOOKUP.

Lookup table is

named GradeTable.

HLOOKUP formula populates G4:G12 by looking up the total value in column F with the first row in GradeTable. Excel stops at the largest value in the table that does not go over the lookup value. Looking for 62.3 would cause Excel to stop at 60 because moving to the next value, 70, would be over the lookup value. Formula in G4 is =HLOOKUP(F4,GradeTable,2).

Financial Functions
• Financial functions can be used for a variety of financial analyses including loan amortizations, annuity payments, investment planning, depreciation, and so on.
• The PMT function is used to calculate a payment for a loan based on a constant interest rate and constant payments for a set period of time.
• Excel provides two related financial functions: PPMT, to calculate the principal portion of the loan payment; and IPMT, to calculate the interest portion.
Financial Functions…continued
• The PPMT function returns the principal portion of a specific payment for a loan.
• The structure of a PPMT function is =PPMT(rate,per,nper,pv,fv,type) where:
• rate is the interest rate per period,
• per is the period for which you want to find the principal portion of the payment,
• nper is the number of payment periods,
• pv is the amount of money borrowed,
• fv is the balance at the end of the loan (if left blank, zero is assumed), and
• type is either 0 (payment at end of period) or 1 (payment at beginning of period).
Financial Functions…continued

To create a PPMT formula:

• Make the desired cell active.
• Click the Formulas tab.
• Click the Financial button in the Function Library group.
• Click the PPMT option.

continues on next slide…

PPMT option

Financial Functions…continued
• Enter the value, cell address, or range name for the interest rate in the Rate text box.
• Enter the number representing the payment to find the principal for in the Per text box.
• Enter the value, cell address, or range name for the total number of payments in the Nper text box.
• Enter the value, cell address, or range name for the amount borrowed in the Pv text box.
• Click OK.

Pvtext box

Logical Functions
• Conditional logic in formulas requires Excel to perform a calculation based on the outcome of a conditional test where one calculation is performed if the test proves true and another calculation is performed if the test proves false.
• If you need Excel to perform more than two actions, create a nested IF function.
• You can nest any function inside of another function.
Logical Functions…continued

To create an IF Formula:

• Make the desired cell active.
• Click the Formulas tab.
• Click the Logical button in the Function Library group.
• Click the IF function.
• Type the conditional test argument in the Logical_test text box.
• Press Tab.
• Type the argument in the Value_if_true text box.
• Press tab.
• Type the argument in the Value_if_false text box.
• Click OK.

Logical_test text box

Logical Functions…continued

To create an AND Formula:

• Make the desired cell active OR nest formula in IF statement Logical_test text box.
• Type =AND( or AND( if nesting in an IF statement.
• Type the first conditional test argument.
• Type a comma.
• Type the second conditional test argument.
• Repeat Steps 4 to 5 for the remaining conditions.
• Type ).

Logical_test text box

Logical Functions…continued

To create an OR Formula:

• Make the desired cell active OR nest formula in IF statement Logical_test text box.
• Type =OR( or OR( if nesting in an IF statement.
• Type the first conditional test argument.
• Type a comma.
• Type the second conditional test argument.
• Repeat Steps 4 to 5 for the remaining conditions.
• Type ).

Logical_test text box

Text Functions

continues on next slide…

Text Functions…continued

To use the substitute text formula:

• Make the desired cell active.
• Type =SUBSTITUTE(.
• Type the source text cell address.
• Type a comma.
• Type the text to be changed in the quotation symbols.
• Type a comma.
• Type the replacement text in quotation symbols.
• Type ).
• Press Enter.

substitute text formula

Text Functions…continued

To convert text to uppercase:

• Make the desired cell active.
• Type =UPPER(.
• Type the source cell address OR type the text to convert in quotation symbols.
• Type ).
• Press Enter.

convert text to uppercase

CHECKPOINT 2
• The Name Manager button is located in this tab.
• Insert
• Formulas
• Home
• Data
• This function returns the principal portion of a specific payment for a loan.
• SPMT
• IPMT
• PMT
• PPMT

Answer

Answer

Next Question

Next Question

• This lookup function is more commonly used since most lookup tables are arranged with comparison data in columns.
• CLOOKUP
• DLOOKUP
• VLOOKUP
• HLOOKUP
• Replace existing text with new text using this function.
• SUBSTITUTE
• PROPER
• UPPER
• LOWER

Answer

Answer

Next Question

Next Slide

Advanced Functions and Formulas

Summary of Presentation Concepts

• Create and use named ranges in formulas
• Use functions COUNTA, COUNTIF, COUNTIFS
• Use functions AVERAGEIF, AVERAGEIFS
• Use functions SUMIF, SUMIFS
• Edit a named range
• Rename and delete a named range
• Look up data using the lookup functions VLOOKUP and HLOOKUP
• Analyze loan payments using PPMT
• Use conditional logic functions IF, AND, and OR
• Modify text using the text functions PROPER, UPPER, LOWER, and SUBSTITUTE