” DEVIATIONS FROM THE MEAN

” CROSS SERIES RELATIONS

” COVARIANCE AND CORRELATION FUNCTIONS

” SUM OF THE SUM OF THE SQUARES OF TWO VARIABLES

143

Statistical Analysis with Excel

” SUM OF THE SQUARES OF DIFFERENCES ACROSS TWO

VARIABLES

” SUM OF THE DIFFERENCE OF THE SQUARES OF TWO

VARIABLES

COUNTING AND SUMMING

8.1

COUNT function

This function counts the number of valid cells in a range. Cells are valid

only if there value is numeric or a date.

Menu path to function: INSERT / FUNCTION / STATISTICAL

/ COUNT.

Data requirements: Numbers and dates are included in the count. Not

counted cells include those that contain error values, text, blank cells, and

logical values (like TRUE and FALSE). The X values can be input as

references to one or more ranges that may be non“adjacent.

The second range can be referenced in the first text-box “Value1” after

placing a comma after the first range, or it could be referenced in the

second text-box “Value2.”

If you use the second text-box, then a third text-box “Value3” will

automatically open. (As you fill the last visible box, another box opens

until the maximum number of boxes ” 30 ” is reached.)

144

Chapter 8: Other Mathematics & Statistics Functions

Table 27: Sample data for the “Count” functions.

The example is in the sample file “Count.xls.”

A B C D

Y Date Respondent is employed

.51 24.34 24” Sep” 2000 TRUE

20.07 24.34 25” Sep” 2000 FALSE

VALUE! 24.34 26” Sep” 2000 #VALUE!

15.28 24.34 27” Sep” 2000 FALSE

#VALUE!

DIV/0! 28” Sep” 2000 TRUE

11.63 24.34 29” Sep” 2000 #N/A!

.86 30” Sep” 2000 TRUE

REF! 22.00 1” Oct” 2000 FALSE

.74 22.00 TRUE

NAME? 22.00 3” Oct” 2000

.13 22.00 4” Oct” 2000 TRUE

N/A! 21.58 5” Oct” 2000 TRUE

Figure 127: COUNT

145

Statistical Analysis with Excel

COUNTA function also counts cells with logical or text values

This function counts the number of valid cells in a range. Valid values

include cells with numeric, date, text, logical, or error value. COUNTA

only excludes empty cells, but text and logical values are only counted if

you type them directly into the list of arguments are counted. If an

argument is a data array or range reference, only numbers in that data

array or range reference.

Figure 128: The function COUNTA is a variant of the COUNT function. The example is in the

sample file “Count.xls.”

Menu path to function: INSERT / FUNCTION / STATISTICAL /

COUNTA.

Data requirements: Unlike the COUNT function, COUNTA will include

the label row in the count. (So, if you have one label in the referenced

range, you may want to use “= COUNTA (A:A) ” 1”.) The X values can be

input as references to one or more ranges that may be non“adjacent. The

second range can be referenced in the first text-box “Value1” after placing

a comma after the first range, or it could be referenced in the second text-

box “Value2.” If you use the second text-box, then a third text-box

“Value3” will automatically open. (As you fill the last visible box, another

box opens until the maximum number of boxes ” 30 ” is reached.) The

function does not count invalid cell values when counting the number of X

values.

146

Chapter 8: Other Mathematics & Statistics Functions

COUNTBLANK function counts the number of empty cells in the range

reference

This function counts the number of blank cells in a range.