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:
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.
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
Back to Excel Add-in, Templates and Training Main Page
Special :
Get the Excel VBA Training for free when you purchase our Technical Indicators now!
|
