The cells in sum range are summed only if their corresponding

cells in the entire Range match the criteria. If sum range is

omitted, all the “criterion-satisfying” cells in the Range are

summed.

Menu path to function: INSERT / FUNCTION / MATH / SUMIF. The

Criteria should be relevant to the type of data/text in the queried range.

COUNTIF function

This function counts the number of cells in a range that satisfy a user-

defined criterion.

The dialog for “COUNTIF“ requires two inputs from the user. The

“Range” is similar to the functions shown previously. The “Criteria” is a

logical condition set by you.

150

Chapter 8: Other Mathematics & Statistics Functions

Figure 134: COUNTIF (counting only the cells whose value satisfies one “if” condition)

• In the box Range, enter a reference to the range of cells you seek to

evaluate.

• In the box Criteria, enter the condition (a number, expression, or

text) that defines which cells will be counted. For example,

Criteria can be expressed as 32, “32,” “>32,” “tea.”

Menu path to function: INSERT /FUNCTION /STATISTICAL /COUNTIF.

Data requirements: The range can take any values. The Criteria should

be relevant to the type of data/text in the queried range.

Example

Choose the range “D:D” and the condition “>1,000,000”. The function is

“Count the number of cases in the range D:D, but only if the value of the

cell is greater than 1 million.”

For a pictorial reproduction of this, see the next figure.

Figure 135: Entering the data input and logical criterion

Execute the dialog by clicking on the button OK. The formula is written

151

Statistical Analysis with Excel

onto the cell. The next figure illustrates this. Depress the ENTER key.

Figure 136: The function as written into the cell

TRANSFORMATIONS (LOG, EXPONENTIAL,

8.3

ABSOLUTE, SUM, ETC)

Table 28: Common transformation functions

Location within

Function Description INSERT Data Requirements

/FUNCTION

This function outputs the

sign of a number.

Returns 1 if the number

is positive, zero (0) if the

number is 0, and “1 if

the number is negative. MATH /SIGN

Sign Any real value.

Useful for red“flagging

data, or using in

functions like IF,

COUNTIF, SUMIF and

CHOOSE.

Absolute

ABS = | X | MATH /ABS One real number.

number

The square root of a One positive real

Square root MATH/SQRT

number. number.

152

Chapter 8: Other Mathematics & Statistics Functions

Location within

Function Description INSERT Data Requirements

/FUNCTION

Y = X1/2

LN (X)

This function calculates the

natural logarithm of a

number. Natural

logarithms are based on

the constant e (2.718). MATH /LN One positive real

Log natural

number.

LN (85) = 4.454347.

This mean: “If you raise the

base e to the power of 4.45

you will get 85. LN (85)