<<

. 16
( 62 .)



>>

The formula returns 3, the number of times numbers appear
in the range B2 : C4.


COUNTA
Unlike COUNT, COUNTA counts both numbers and text. In the
illustration above, COUNTA(B2 : C4) would return the number 6.


COUNTIF
This is a combination conditional and counting function. It is
similar to the SUMIF but returns a count of the items that
meet the condition, rather than the sum. It only has two argu-
ments. The syntax is:

= COUNTIF°Range,CriteriaÞ

Range is a list of items. Criteria is a condition related to
Range. The following shows the number of items related to the
category ˜˜Alex.™™

= =COUNTIF(B2:B7,E2)
F2
A B C D E F G
1
Alex 32 Alex 2
2
John 27
3
Alex 19
4
Lena 43
5
Hillary 51
6
Alex 22
7
8




TLFeBOOK
Your Model-Building Toolbox: Functions 83




If you want to count the number of items that are equal (or
above or below) a certain value, you can also use COUNTIF.
In this case, the Criteria must be stated as a test. The following
example shows the number of items that are above 25 and looks
to the range C2:C7.

= =COUNTIF(C2:C7,E2)
F2
A B C D E F G
1
Alex 32 >25 4
2
John 27
3
Alex 19
4
Lena 43
5
Hillary 51
6
Alex 22
7
8



AVERAGE
This is really the SUM and COUNT functions together, or even
more basic, the sum of the elements, divided by the number of
elements. The syntax is:

= AVERAGE°number1,number2,. . . number30Þ

The number arguments are numbers, arrays, or references
that contain numbers. If the argument is a text, AVERAGE will
consider it the same as a blank. Some points to note when using
AVERAGE:
A blank cell is totally disregarded in the averaging
u

calculations. A cell with a 0 is part of the calculations.
This is shown in the following illustration:

A B C D E F G
Average
1
10 2 6 =AVERAGE(A2:C2:)
2
3
10 0 2 4 =AVERAGE(A4:C4)
4
5
6




TLFeBOOK
Chapter 5
84




If the elements all point to blank cells, you™ll have a
u

#DIV/0! error. This is the result of the denominator
count™s being zero. However, if they point to at least one
cell that contains the value 0, then the function will work.


FUNCTIONS FOR DATES
Dates can be simple four-digit numbers that go up by 1 with
each column, representing years. Occasionally, you may have to
enter quarterly dates or work with days, months, and years. If
we want to have a good control of the dates in the column so
that we can easily change them, then we will have to understand
how dates work in Excel.


How Excel Keeps Track of Dates
Excel keeps track of dates by assigning a number, or a serial value,
to a date, starting with the number 1 for January 1, 1900. The
upper limit is the serial value 2,957,063 for December 31, 9999.
Excel™s formatting takes this another step. By using different data
formats, you can make the serial value appear in the date format
you want, including non-U.S. dates (˜˜31/12/2003™™), or even as
times of the day or the day of the week.


Monthly Dating
Here is a simple dating problem: How do we make a date go up
by one month? Or more specifically, how do we get from the end
of one month to the end of the next month?
Even with serial values for dates, we cannot just add 30 or
30.42 (that™s 365/12) to a starting date if we want to make it go
up by one month at a time. Adding 30.42 to December 31, 2003
will give us January 30, 2004, and not January 31. Adding 30.42
to January 31 will give us March 1, not the end of February.
The solution is to work with years, months, and days. If we
want to go up one month, we simply add 1 to the month desig-
nator, no matter what the length of the month is. Likewise, to go
up one year, we add 1 to the year designator, leap year or not.
We can do this in Excel, because Excel will show you what year,




TLFeBOOK
Your Model-Building Toolbox: Functions 85




month, or day it is for any serial value representing any time
between January 1, 1900 and December 31, 9999. The functions to
use are YEAR, MONTH, and DAY.
Thus, if we picked a serial value like 37986:

= YEAR°37986Þ

returns 2003;

= MONTH°37986Þ

returns 12; and

= DAY°37986Þ

returns 31.
This is to say that December 31, 2003 is 37,986 days away
from December 31, 1899 (January 1, 1900, being day 1 in the
serial value, is 1 day away). We would get the same results if
we actually used the date 12/31/2003:

= YEAR°“12=31=2003”Þ

returns 2003;

= MONTH°“12=31=2003”Þ

returns 6; and

= DAY°“12=31=2003”Þ

returns 16.
Note: I am using here the U.S. convention for dates, which
uses the order of month/day/year. Excel can be set to show
different dating formats so that the serial value will appear
with the correct order of days, months, and years. Go to
Format > Cells > Number and select Date in the ˜˜Category™™ list
box. Look at the drop-down box for ˜˜Locale (location).™™




TLFeBOOK
Chapter 5
86




You will have to use the double quotes on the date to mark
it as a text string. However, if the date were placed in another
cell and the function referenced that cell, you do not have to
worry about double quotes.
Going the other way, we can write a date using the compo-
nent parts of year, month, and day. For this, there is the DATE
function.

= DATE°2003,12,31Þ

This returns the serial value 37986, which can be formatted
to appear as 12/31/2003.
Because Excel functions can use the results of other func-
tions, we can write the following in cell C1. Let™s put 12/31/2003
in a separate cell, say, cell B1:

= DATE°YEAR°B1Þ,MONTH°B1Þ,DAY°B1ÞÞ

This returns the same date: 12/31/2003. Now, let™s return to
the dating problem we had at the beginning of the chapter: How
do you make a date go up by one month?




TLFeBOOK
Your Model-Building Toolbox: Functions 87




We start with 12/31/2003 in B1. To make it go up by one
month, we add 1 to the MONTH. In C1, we write:

= DATE°YEAR°B1Þ,MONTH°B1Þ+1,DAY°B1ÞÞ

This will return 1/31/2004 (January 31, 2004). Success!
Building on this, let™s try the next column again. In D1 we add
another digit to the MONTH(C1):

= DATE°YEAR°C1Þ,MONTH°C1Þ+1,DAY°C1ÞÞ

This returns 3/2/2004 (March 2, 2004). This is not quite
right. What™s happening? The problem arises because January
has 31 days and, with this formula, we are asking Excel to
give the date for something like February 31, 2004. (We had no
trouble with the first formula for January, because that month has
the same number of days as December.) Since February in this
leap year only has 29 days, Excel keeps counting until the ˜˜day
31™™ of February, and comes up with the equivalent March 2.
How do we find the ending day of each month, given that
months™ lengths vary? Instead of trying to find the ending day of
each month, we could look for the first day of the next month and
then subtract one day. Since the first day is always day 1, this
is quite easy. So January 31 is really February 1 minus 1 day;
February 28 is March 1 minus 1 day, and so on. But wait. Since
Excel can deal with something like ˜˜February 31™™ to return
March 2 (or March 3 in a non-leap year), can Excel consider

<<

. 16
( 62 .)



>>