Lesson 26 — Activity 3: Advanced Formulas



Getting Ready


Now that you have learned how to enter simple formulas into an Excel spreadsheet, you will learn about more advanced formulas in this activity.


Let's take a look at some other ways of calculating answers to questions using spreadsheets.

Typing formulas into cells can be a lot of work, especially if there are a lot of numbers. Another way is to combine typing and using your mouse to enter a formula into a cell. This formula is called =SUM.

Let's say you need to add the numbers in column A and put the answer in B3.

You have already seen that in cell B3 you could type in =A1+A2+A3. This is fairly easy to do when you are only adding a few numbers, but what if you had 100 numbers to add up? That would be a lot of typing!

Another way to enter that formula in cell B3 would be to use a symbol in the toolbar that tells the spreadsheet to use all of the numbers between the first and last one that you enter.


These are the steps to follow:

  1. Place the cursor in cell B3, and you will see a box opens up.
  2. Click on this symbol Σ at the top right-hand side of the toolbar.
  3. Click on cell B4 and this is what should appear =SUM().
  4. In between the brackets, type in the first cell number, then a colon, and then type in the last cell name. You should now have an equation that looks like this: =SUM(A1:A4).
  5. Hit enter and the sum of all of the numbers in cells A1 to A3 automatically appear!


Your spreadsheet should now look like this:



Another formula that you could use is called =AVERAGE. This formula lets you figure out the average of a group of numbers.

If you wanted the average of the numbers in column A of the spreadsheet above, you would simple move to the cell you want the answer in and create your formula. You must choose a different cell to B3.

The steps to use in this formula are exactly the same as for =SUM except for Step 2.

This is the new Step 2: scroll down on the arrow beside this symbol Σ and click on the word Average.

Then follow Steps 3, 4, and 5. You should end up with this formula:

=AVERAGE(A1:A3)


(NOTE: Ignore any moving lines, etc. that may appear. If you enter the formula correctly, these will disappear when you hit enter.)

What good is a formula like this? You could use it to figure out an average temperature, the average amount of calories you are eating, or your mark in a class!

 

You can also use formulas to figure out the lowest, or minimum, number in a group. If you wanted the average of the numbers in column A of the spreadsheet above, you would simple move to the cell you want the answer in and create your formula.

The steps to use in this formula are exactly the same as for =AVERAGE except for Step 2.

This is the new Step 2: scroll down on the arrow beside this symbol Σ and click on the word Min.

Then follow Steps 3, 4, and 5. You should end up with this formula:

=MIN(A1:A3)


(NOTE: Ignore any moving lines, etc. that may appear. If you enter the formula correctly, these will disappear when you hit enter.)


You can also follow the same steps to figure out the largest, or maximum, number by clicking on Max when you scroll down on the arrow. You should end up with this formula:

=MAX(A1:A3)

  Self-check!

Try this!


Practise working with these new spreadsheet commands.

You are given this spreadsheet. What formula would you use to figure out an average and put it in cell C1?



If you have numbers that start in cell B3 and end in cell B53, how can you find out what the sum (the total) is if you want the answer in cell C60?


Move to cell C60 and create the formula:
=sum(B3:B53)


What would the formula =min(D15:D73) do?


The smallest number in the list. =min means find the minimum number in the list.


Digging Deeper


Click on the Play button below to watch a video on some of the more advanced formulas in an Excel spreadsheet.



Courtesy of ADLC