USEFUL FUNCTIONS & THE INSERT FUNCTION DIALOG

LESSON WORKBOOK:  Level 1 Lesson 5 2007.xlsx

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 dialog, located to the left of the formula bar (the Fx).  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 dialog and make life easier for us all, it is because we firmly believe that the most important aspects of Excel and it’s functions and formulas is understanding them. We would like to believe that by course completion we will have taught you Excel, not shown it to you!

The functions we shall look at first are:

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 we will identify these in the syntax description by not bolding the argument. For example the SUM function can take up to 255 arguments, but only one of the 255 needs to be supplied, so we would show this as below, eg:

Syntax SUM(number1,number2,...) Meaning “number2,…. to number 255” 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 255 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 255 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/2008, house, 0, dog

COUNTA

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

Syntax

COUNTA(value1,value2, ...)

The COUNTA function takes up to 255 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/2008, 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 255 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 255 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 and A5 would be summed as they meet the criteria of being greater than 5.

Insert Function

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:

Once activated you will see the Insert Function dialog pop up in front of you. 

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 the underlined words (probably blue) 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.  Finally click on the words Help on this 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 our 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.

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 of using the Insert Function for the SUMIF is the same principle we would use for all Functions written by using the Insert function dialog.  As we 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

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

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, above column A. 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 Name Manager dialog box.

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 Name Manager 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/2008 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.

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.

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 Names 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.

 

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.

OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.