Other Excel Add-In, Templates and Training

Lesson 5

USEFUL FUNCTIONS & THE INSERT FUNCTION

Download Workbook for this lesson

Now we have covered the SUM function in detail and also covered formula arguments and formula syntax we will use half of this lesson to look at some of Excels easy to use Functions. Although these functions are among the easiest to use they are also arguably the most useful.

Once we have covered these functions we will go into some detail on Excels Insert Function. The Insert Function was known as the Paste Function in earlier versions of Excel.  The Insert Function houses all of Excel’s built-in functions under their appropriate categories and goes a long way to writing the chosen function for us. In case you are wondering why we don’t just skip all the detail and go straight to the Insert Function and make life easier for us all, it is because I firmly believe that the most important aspects of Excel and it’s functions and formulas is understanding them. I like to believe that by course completion I will have taught you Excel, not shown it to you!

The functions we shall look at first are:

  • AVERAGE

  • COUNT

  • COUNTA

  • COUNTBLANK

  • COUNTIF

  • MAX

  • MIN

  • SUMIF

We will start each description with what the function does, followed by its syntax and then the number of arguments it can accept. It is important to note that while some functions take more than one argument it is not always the case that they must all be supplied.

These arguments are known as option arguments and I will identify these in the syntax description by not bolding the argument. For example the SUM function can take up to 30 arguments, but only one of the thirty needs to be supplied, so I would show this as below, eg:

Syntax SUM(number1,number2,...) Meaning “number2,…. to number 30” are all optional arguments while “number1” must be supplied.

AVERAGE

The AVERAGE function is used to return the average of the arguments supplied.

Syntax

AVERAGE(number1,number2, ...)

The AVERAGE function can take up to 30 arguments.

The arguments supplied must be numeric or references to numeric values. Text and/or references to text are ignored. It is important to note that cells containing zeros are NOT ignored. This can give you unexpected results if you are not aware of it.

=AVERAGE(A1:A3) would equal 10 if A1:A3 contained 5, 10, 15 respectively

COUNT

The COUNT function is used to count numbers or references to numbers in a range.

Syntax

COUNT(value1,value2, ...)

The COUNT function takes up to 30 arguments and each argument can be a variety of data types, but only numbers are counted.

If the range reference supplied contains valid dates these will also be counted.

=COUNT(A1:A5) would equal 3 if cells A1:A5 contained 10, 12/12/2001, house, 0, dog

COUNTA

The COUNTA function is used to count non-empty cells.

Syntax

COUNTA(value1,value2, ...)

The COUNTA function takes up to 30 arguments and each argument should be a reference to a range. Cells within the range can be a variety of data types, but only non-empty cells are counted.

=COUNTA(A1:A5) would equal 4 if cells A1:A5 contained , 12/12/2001, house, 0, dog. 

In other words A1 is empty and so is not counted while all other cells are.

COUNTBLANK

The COUNTBLANK function is used to count empty cells. It is the opposite of the COUNTA function

Syntax

COUNTBLANK(range)

The COUNTBLANK function takes 1 argument and this argument should be a reference to a range. Cells within the range can be a variety of data types, but only empty cells are counted

=COUNTBLANK(A1:A5) would equal 1 if cells A1:A5 contained , 12/12/2001, house, 0, dog

In other words A1 is empty and so is counted while all other cells are not.

COUNTIF

The COUNTIF function is used to count cells within a range that meet a specified criterion.

Syntax

COUNTIF(range,criteria)

The COUNTIF function takes two (2) arguments. The range argument is a reference to a range of cells, while the criteria argument is the criterion that should be met by the cells within range before they are counted. The criteria specified can be in the form of a number, text or an expression.

Number criteria

=COUNTIF(A1:A5,20) would equal 1 if cells A1:A5 contained 15, 22, 20, 0, dog

In other words A3 is the only cell that meets the criteria of 20

Text criteria

=COUNTIF(A1:A5,”dog”) would equal 1 if cells A1:A5 contained 15, 22, 20, 0, dog

In other words A5 is the only cell that meets the criteria of “dog

Expression criteria

=COUNTIF(A1:A5,”<20”) would equal 2 if cells A1:A5 contained 15, 22, 20, 0, dog.

In other words A1 and A4 are the only cells that meets the criteria of “<20”.

MAX

The MAX function is used to return the largest number from a set of values.

Syntax

MAX(number1,number2,...)

The MAX function takes up to 30 arguments and will ignore text.

=MAX(A1:A5) would equal 10 if cells A1:A5 contained 9, 8, house, 10, -10

MIN

Opposite to Max, the MIN function is used to return the smallest number from a set of values.

Syntax

MIN(number1,number2,...)

The MIN function takes up to 30 arguments and will ignore text.

=MIN(A1:A5) would equal 1 if cells A1:A5 contained 9, 8, house, 10, 1.

SUMIF

The SUMIF function is used to return the sum value from a specified range that meets a criterion.

Syntax

SUMIF(range,criteria,sum_range)

The SUMIF takes up to 3 arguments. The range is the range of cells to evaluate to see if they meet the specified criteria. The criteria specified can be in the form of a number, text or an expression. The sum_range is the range of cells to sum, but only if the corresponding cells in the range meet the specified criteria. If sum_range is omitted then the cells within the range are summed.

=SUMIF(A1:A5,5) would equal 10 if cells A1:A5 contained 5, 8, house, 10, 5

In other words cells A1 and A5 would be summed as they meet the criteria and NO sum_range was supplied.

=SUMIF(A1:A5,5,B1:B5) would equal 20 if cells A1:A5 contained 5, 8, 1, 9, 5 and cells B1:B5 contained 10,1,3,8,10.

In other words cells B1 and B5 would be summed as the corresponding cells in A1:A5 have a value of 5.

=SUMIF(A1:A5,”Cat”,B1:B5) would equal 15 if cells A1:A5 contained Cat, cat, Cat, 9, 5 and cells B1:B5 contained 5,5,5,8,11

In other words cells B1,B2 and B3 would be summed as the corresponding cells in A1:A5 contain the text “Cat” (not case sensitive).

=SUMIF(A1:A5,”>5”) would equal 34 if cells A1:A5 contained 10, 15, Cat, 9, 5 and In other words cells A1, A2 and A4 would be summed as they meet the criteria of being greater than 5.

Insert Function

Called the Paste Function dialog in older versions, but the Insert Function dialog in newer versions, this dialog box is used to insert or paste the selected function into the chosen cell. The big advantage to using this feature comes as you become more comfortable writing Excel formulas. Initially it is most beneficial because it can be used as a step-by-step guide for each argument in a function. What this means is, if you are going to be using a simple function such as the SUM, MIN, MAX etc., it really serves no purpose. When writing slightly harder functions such as COUNTIF, SUMIF etc., it can aid greatly.

Let’s display the Insert Function dialog and have a superficial look at it. There are three methods we can use to show this dialog box and which one you use is purely optional. The three methods are:

  • Going to Insert>Function

  • Push Shift + F3

  • Click the Insert Function icon to the left of your Formula bar (Fx), or for older version users, click the Paste Function icon on your Standard toolbar.

Once activated you will see the Insert Function dialog pop up in front of you.  Depending on which version of Excel you are using, these heading names may vary slightly in this dialog box.

Search for a Function

Type a brief description of what you want to do in this box, then click Go to view a list of appropriate Functions.

Or Select A Category

In this dialog box you will see the Category Names that the Functions are grouped in.  Click All to see a list of All Functions displayed in the Select A Function: box in alphabetical order.  Click Most Recently Used to see a list of the last 10 functions used in the Select a Function: box.  

Help

In the bottom left hand corner you will see either Excels standard help button (question mark), or the words Help on this Function.  If you click this you will be presented with a description of how the selected Function works from Excel's Help.

To see how this works, Select All Under Or Select a Category: then Click on SUMIF under Select A Function.

The help screen that is displayed will give you most of the relevant information for the selected function, in this case SUMIF .  All function help descriptions are uniform in that they will show the syntax, a description of the arguments and an example. We strongly recommend that you familiarise yourself with the Function Help as it can be very helpful once you are aware of the terminology used, hence my explanations on arguments, syntax, ranges, text values etc.

Most Excel users shudder at the thought of using the help to get their answers, but this is most likely because they feel intimidated by the jargon Excel uses. Please do your utmost to not become one of these, as the Excel help will always be your best source of help. If there are any terms used by the Excel help you are uncertain of you can always ask us.

For now close the SUMIF help and click the Cancel on the Insert Function dialog box. Let’s try this simple exercise to see how the Insert Function can help in writing a formula. The purpose of this exercise is more to show you how to use the Insert Function as opposed to the SUMIF function itself.

  • Type the numbers 1, 5, 20, 40, 50, 100, 200 in cell A1:A7 respectively

  • Type the names Bill, Bob, Dave, John, Fred, Mary, Jill in cells B1:B7 respectively.

  • Select cell H2 and go to Insert>Function or push Shift+F3 this will display the Insert Function dialog box.

  • Click the Math & Trig in the Or Select a Cateogry: box.

  • Scroll down and select SUMIF.  At the bottom of the Select a Function: box will be the SUMIF arguments and syntax as well as a brief description of what it does.

  • Click the OK button to display the Function Arguments dialog for the SUMIF. You will notice there is one box for each argument and each box has the argument written to the left of its box. Notice the last argument (Sum_range) is not bold. This is because the argument is optional. Meaning, if we omit this argument, Excel will use the Range argument as the range to sum as well as the range to meet the Criteria.

  • Your mouse insertion point should be within the Range argument box, if it's not then click in there. If you look near the bottom you will see a brief description of what the Range argument does, which is the range of cells to evaluate based on the Criteria.

  • Look in your Formula bar and you will see =SUMIF() this is the SUMIF function with its opening and closing parenthesis. It is within these parentheses that the arguments will be placed.

  • Click the small box on the right of the Range argument box; this is called the collapse dialog button . The SUMIF dialog will disappear. Click in cell A1 and holding down the mouse button drag down to cell A7.

  • Now click the small button again, this is now the expand dialog button and the SUMIF dialog will appear again. You should now have A1:A7 as the Range argument.

  • Click in the Criteria argument box and you will see a brief description of what the Criteria argument expects for its data. We are going to use an expression first.

  • Type ">50" (including quotations) and you will see = 300 in the dialog box.  This is the result of the Function, which in this case is the SUMIF.  At the bottom of the dialog box you will see Formula result = 300. In this case the Function result and the Formula result will always be the same. There would only be a difference if we were doing what is known as Nesting. This is something we will look at in a later lesson.

  • So the result 300 is what we would expect in this instance as the only numbers within our Range greater than 300 are 100 and 200. There was no need to supply any data for the Sum_range in this case because we were using the Range as the cells to sum as well as the cells to match the Criteria.

  • Delete the ">50" Criteria and replace it with "B*". You will notice both the Function result and Formula result =0 This is because the SUMIF is trying to use the Range cells to match the Criteria and for the cells to sum.

  • What we need to do now is have Excel look in a different Range to meet our Criteria, we also need to supply a Sum_range argument.

  • Click on the collapse dialog button for the Range argument and select range B1:B7, click the expand dialog button. We should now have the range B1:B7 as our Range argument. Again the result for both the Function and Formula will still be 0 (zero).  Now we need to supply a Sum_range argument!

  • Click on the collapse dialog button for the Sum_range argument and select range A1:A7, click the expand dialog button. We should now have the range A1:A7 as our Sum_range argument. Click OK we will get the result of 6.

This is because we have told the SUMIF to sum all cells in the range A1:A7 if the corresponding cell in B1:B7 has a word beginning with the letter "B".

The method in which we used the Insert Function for the SUMIF is the same principle we would use for all Functions written by using the Insert function dialog.  As I stated before we began the above steps, the point of the exercise was to demonstrate the way in which the Insert Function can aid us in writing formulas.

Naming Ranges

Excel allows us to give Worksheet ranges names that can make our formulas easier to read. For instance if we use the above example that we used for the SUMIF Function, we could name our Criteria range (B1:B7) "Names" and our Sum_range (A1:A7) "Amounts". This would make our formula a bit easier to read.

There are however some basic rules for naming cells that we must adhere to. These are listed below and are from the Excel help file.

Guidelines for naming cells, formulas, and constants in Microsoft Excel

  • The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.

  • Names cannot be the same as a cell reference, such as Z$100 or R1C1.

  • Spaces are not allowed. Underscore characters and periods may be used as word separators ¾ for example, First.Quarter or Sales_Tax.

  • A name can contain up to 255 characters.

  • Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

There are a couple of ways we can name ranges so let's jump straight in with an example.

  • Delete the contents of cells A1 and B1 and type the word Amounts in cell A1 and Names in cell B1.

  • Type 5, 20, 40, 50, 100, 200 in cell A2:A7 respectively. Type the names Bob, Dave, John, Fred, Dick, Jill in cells B2:B7 respectively, if they are not there already.

  • Now either select the range A1:B7 with the mouse, or push Ctrl + Shift + * this will make Excel select the Current region. The Current region is defined as all the non-empty adjoining cells surrounding the active cell. The * (asterisk) must be the one on the same key as the 8) .

  • Go to Insert>Name>Create. This is the Create names dialog box and is used to create names based on the current region row and/or column headings. In this case we only have column headings so ensure that only the "Top row" check box is checked.

  • What we have done by doing this is told Excel that we wish to name the range A2:A7 Amounts and the range B2:B7 Names. In other words use the headings in the top row to name the selected ranges directly below them. Click OK

  • Now select cell C8 and push Shift + F3 or go to Insert>Function. Locate the SUMIF function either from within the category Most recently used or Math & Trig then click OK.

  • Click the collapse dialog button on the Range argument box and select range B2:B7. You should see Excel place the name Names in the Range argument box. This is our named range B2:B7. Click the expand dialog button.

  • Type "D*" in the Criteria argument box.

  • Click the collapse dialog button on the Sum_range argument box and select range A2:A7. You should again see Excel place the name Amounts, this time in the Sum_range argument box. Click the expand dialog button. Now Click OK.

You should have the formula =SUMIF(Names,"D*",Amounts) giving the result of 120.

Using the Create names dialog is the easiest method to use if we are going to be calling our ranges the same names as the column and/or row headings as it saves typing and typos. We could, if we wanted, type the names in ourselves, by typing them directly into the Name box. The Name box is on the left of the Formula bar. If we click the small drop arrow on the Name box, we should see both our named ranges Amounts and Names. If you select either one Excel will take you straight to the chosen named range and select it. It is in this box that we can type a name in directly.

To see what I mean select the range A1:B7 and click in the Name box. Type the word Data and push Enter.  Now select any cell outside of range A1:B7 and select the name Data from the Name box, you will be transferred automatically to the Data selection.

The other thing we need to know when dealing with named ranges is how to delete them. This can only be done in one way and this is via the Insert name dialog box.

  • Go to Insert>Name>Define or push Ctrl + F3. This will display our Insert name dialog box.

  • You should see the three names we had created amongst the listing. Select the name Amounts.

  • If you now look in the Refers to box you should see =Sheet1!$A$2:$A$7.  The Sheet1! May be different if the Worksheet is called something else.

  • All we need to do now is click Delete and the named range Amounts will be deleted.

  • Do the same for Data and Names then click OK.

You will notice that our SUMIF formula is now retuning #NAME? This is Excels way of telling us the formula within the cell contains text is does not recognise.  Delete the #NAME?

The Insert name dialog is also the only place we can edit named ranges. In the above example we could have altered the Refers to range for any of the names to another range. For example we could have changed =Sheet1!$A$2:$A$7 to =Sheet2!$A$2:$A$7 and/or =Sheet2!$L$1:$M$70 or any valid range address.

Constants

There is one other area of Naming we would like to cover and this is naming CONSTANTS. A constant is simply a value that is not the result of a formula. So if we type 10% or David or 12/12/2000 into a cell these would all be constants, as their values would not change unless we changed them.

Quite often when setting up a spreadsheet some users will type a value or text that they will be using a lot into a cell and then refer to that cell in their formulas. For example lets say we are setting up a spreadsheet that will constantly be using the percentage 10%.  We could place this into a cell somewhere and then name this cell TenPercent. We could then use this named cell in all our calculations that require 10%.  Let's try this to see how it works.

  • Type the value 10% in any cell.

  • With this cell selected go to the Name box and type in TenPercent and push Enter.

  • Now in any other cell type =100*TenPercent and push Enter.

You should have the result 10, which is what we would expect by multiplying 100 by 10%. The biggest advantage to this method is that if we need to change 10% to say 20% we simply go to our named cell TenPercent and type in 20%. All our formulas that are using TenPercent in their calculations will change accordingly. The down side to this method is that it is all too easy to accidentally delete the value in our named cell.  A better method is the one below.

  • Go to Insert>Name>Define (or push Ctrl + F3)

  • Select TenPercent and then in the Refers to box type 10% straight over the top of the cell address.

  • Click Add then OK.

What we have now done is named a constant. This is a better method as it is very unlikely this could be accidentally deleted.

Paste Name Dialog

You may end up with a Workbook that has many named constants and it is a bit hard to remember them all! Not to worry as Excel has a feature called the Paste name dialog. This can both insert a named constant or range and create a list of all our names within the Workbook. We can see this best if we create some named ranges and some more named constants. Do this using the methods above. When you have created 3 named constants and 3 named ranges try this.

  • Type 0 in any cell then before pushing Enter push F3 or go to Insert>Name>Paste .

  • From the Paste name dialog box select TenPercent and click OK

  • Excel will insert the name for you. This can be very handy when using named ranges and/or constants in formulas.

  • On a new Worksheet select any cell and push F3 and click the Paste list button.  Excel will create a list of all named ranges and constants in the Workbook. The names will be in one column and what or where they refer to in the other. The only reason we suggested a new Worksheet is so that the list is not pasted over the top of existing data.

Goto Next Lesson