MO LESSON 2.8 2024

Warm Up: Each day we will begin with a question or activity to get us started!

Learners can:

• demonstrate how to carry out calculations using functions
• use the function wizard in Excel
• identify errors in functions

Monday, Wednesday, Friday 2:20-3:05 pm

Welcome to Microsoft Office Mrs. Vandernick lvandernick@ccaeducate.me (717) 710-3300 extension 11814

The Plan for Today: Unit 2 Lesson: 2.8 Apply Functions Goals:

Back

Next

• AutoSum
• AVERAGE function
• COUNT function
• Functions
• function wizard
• MAX function
• MIN function
• SUM function

Vocabulary

Back

Next

Back

Next

• Excel has tools called Functions that make calculations easier. They are like shortcuts for math, so you don't need to write long equations. With just a few clicks, you can quickly get the answers you need, making it easier to handle a lot of data.
• Function Structure:
• A formula must follow a specific structure, as shown here: =Function Name (Cell Range)
• The structure of a formula begins with an equal sign (=), followed by the function name, an opening parenthesis, the range of cells, and a closing parenthesis.
• If the structure of the formula is not correctly followed, Excel will display error messages, like we practiced the other day.

Functions in Excel

To write a formula with the SUM function, the following steps need to be followed:

• Type =.
• Type SUM.
• Select or type the cell range.

Which of the following would be the correct formula written with the SUM function to accomplish this task?

1. = SUM (A1 : A6)
2. SUM (A1 : A6)
3. = SUM (A1−A6)
4. =SUM(A3:A5)

Smith has entered some values in Column A of the worksheet as shown below. He wants to add three numbers—357, 369, and 784 and shows the result in cell A7.

SUM Function

Back

Next

• The COUNT function in Excel counts the number of cells that contain numbers in a given range.
• It's useful when you want to know how many cells have numerical data, excluding empty cells or cells with text.
• For example:=COUNT(A1:A10) will count how many cells in the range A1 to A10 contain numbers.

COUNT Function

Back

Next

• The AVERAGE function in Excel calculates the average (mean) of a range of numbers.
• It adds up the numbers in the selected cells and divides the total by the number of values.
• For example:=AVERAGE(A1:A10) will find the average of the numbers in cells A1 to A10.

AVERAGE Function

Back

Next

Back

Next

You have been asked to find the mean of the list of numbers. Which function will help you find the correct answer?

Question

• The MAX function in Excel finds the largest (maximum) value in a given range of numbers.
• For example:=MAX(A1:A10) will return the highest number in the range A1 to A10.

MAX Function

Back

Next

Back

Next

• The MIN function in Excel finds the smallest (minimum) value in a given range of numbers.
• For example:=MIN(A1:A10) will return the lowest number in the range A1 to A10.

MIN Function

• Which function returns the total number of elements in the selected cell range?

• COUNT
• MIN
• AVERAGE
• SUM

Question

Back

Next

• AutoSum in Excel is a quick tool that automatically adds up a range of numbers.
• When you click the AutoSum button, Excel inserts the SUM function to total the values in selected cells.
• For example: Clicking AutoSum after selecting a column of numbers will automatically generate a formula like =SUM(A1:A10) to sum up those cells.

AutoSum Feature

Back

Next

• The Function Wizard in Excel is a tool that helps you easily insert functions into your worksheet.
• It guides you step-by-step through selecting a function, entering the required inputs, and seeing the result.
• This is especially useful if you're unfamiliar with the function or want to avoid manually typing the formula.
• To access it, you can click on the "fx" button next to the formula bar.
• You can also type an equal sign (=) in a cell, and Excel will show the "Insert Function" button or a list of recently used functions.

The Function Wizard

Back

Next

Let’s quickly review some of the key errors and troubleshooting techniques you learned for formulas:

• #DIV/0! - Occurs when trying to divide a number by zero, which is an invalid operation.
• #REF! - Indicates an invalid cell reference, which can occur when referencing cells that have been deleted or moved.
• #VALUE! - Happens when a formula receives the wrong type of argument or operand.

To troubleshoot these errors, you learned to:

• Check for syntax errors, such as missing parentheses or incorrect operator usage.
• Verify cell references and ensure they are pointing to the intended cells.

Troubleshooting Errors with Functions

Back

Next

While functions can encounter some of the same errors as formulas, there are a few errors and troubleshooting techniques that are specific to functions in Excel. Function-Specific Errors:

• #NAME? - This error occurs when Excel does not recognize the function name used in the formula. For example, using an incorrect or misspelled function name like =SUME(A1:A10) instead of =SUM(A1:A10).
• #NUM! - This error can occur when a function receives invalid numeric arguments or produces an invalid numeric result. For example, using =SQRT(-4) which tries to calculate the square root of a negative number.
• #VALUE! - This error can also happen when a function receives the wrong type of argument or operand.

Troubleshooting Errors with Functions

Back

Next

Here are some tips for troubleshooting Excel functions:

• Check the function name: Make sure it's spelled correctly and is a valid Excel function. Function names aren't case-sensitive, but proper capitalization is helpful.
• Verify argument order and types: Ensure the arguments are in the correct order and match the required data types.
• Consult documentation: If unsure, check Excel’s help resources or Microsoft's website for detailed info on the function.
• Use the Function Wizard: It helps you build functions by guiding you through the required inputs with examples.

Troubleshooting Errors with Functions

Back

Next

Question

Back

Next

Question

Back

Next

Question

Back

Next

• What type of error occurs in Excel when you try to use an incorrect or misspelled function name?
• #NUM! error
• #VALUE! Error
• #NAME? error
• #DIV/0! error

Question

Back

Next

Today you learned:

• to use pre-defined functions such as COUNT, SUM, MAX, MIN, and AVERAGE;
• to use the Function Wizard tool in Excel;
• how to identify errors in functions; and

In an upcoming lesson, you will learn about creating charts in Excel.

Summary

Back

Home