ñòð. 16 |

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 |