MS Excel Lesson 9

Lesson plan – Microsoft Excel

Class 9

Objectives:

• Use functions

1)      Before starting with how to insert a function in Excel, it’s important to explain to the class exactly what a function is and how the syntax works. Use a flipchart, dry erase board or whatever you have available. Remind the class of what a formula is and how it is written, giving an example (=A1+B1). There is another way to perform this exact same calculation with a function. Now write the equivalent function (=SUM(A1;B1) or =SOMA(A1;B1) in Portuguese). Compare:

a)      Both start with =

b)      Instead of expressing the calculation with an operator, it uses the name of the function, i.e. SUM instead of +, which must come immediately after the =

c)      The constants and cell addresses are now inside a set of parentheses instead of in an equation. They are now called arguments (argumentos), but they serve the same function. Arguments are separated by semi-colons (ponto e virgola)

i)        Functions can have multiple arguments. An example would be =SUM(A1;B1;C1;D1). Other functions relevant for today are PRODUCT (PRODUTO) and AVERAGE (MÉDIA).

2)      Fortunately, we don’t have to type all this out by hand every time we need to use a function. Open up Excel to begin with inserting functions in a spreadsheet. Make two columns of half a dozen random numbers or so to use to demonstrate and ask the class to do the same.

3)      Start with a simple function by finding the sum of the first pair of numbers. Select the cell (remind them that the cell you select is where the result of the function will appear) and click on the Insert function (Inserir função) button. There are two steps:

a)      Choose the function: Use the dropdown menu to choose the category first. Then, in the scroll window, find the name of the function you want. For more complex functions, there is a small description that appears below the list when you highlight it. SUM() is in Math and Trigonometry (Matemática e trigonometria). Click OK.

b)      Choose the arguments: These can be numbers or cell addresses, just like in a formula. The simplest way to do this is to simply write them in the blank lines, one per line. However, it is somewhat faster using the mouse. In each line, simply click on the cell that you want to use for an argument and Excel will fill in the address automatically.

4)      When you have created a simple function, there are a couple things to point out for the class. First, note that what appears in the cell is different than the actual content, just like a formula. In the formula toolbar, you can see the function itself, while the result appears in the cell. Now break down the meaning of the function, as it appears in the formula bar. There is the name of the function that describes what it is doing, and then in the parentheses, there are all the numbers that the function is including in its calculations.

5)      Once the class has a good idea of what a function is and how it works, repeat (3) with the class following along this time as you do a simple sum. Have them do the same with the rest of the numbers, using SUM(), PRODUCT() and AVERAGE() as the functions.

6)      Now, with the two columns of numbers, imagine that we want to find the sum of one and the average of the other. When creating the function, we could of course click on each argument individually, one for each line. However, there is an easier way. When it comes time to select arguments, simple click and drag over all the cells you want to include (if they are contiguous). In the argument box, the addresses of two cells will appear, separated by a colon, such as A1:A9. The colon means “up to,” so in the example, it would be cell A1 up to A9, a block of nine cells. Demonstrate this by using SUM() on the first column and AVERAGE() on the second. Ask the class to duplicate it.

7)      Hand out Exercise 9. You could easily do this exercise with formulas, but ask them to use functions instead. Exercises 10 and 11 can be used for a day of practice before moving on to the next lesson.

Example to duplicate in class: