стр. 42 |

Menu path to function: INSERT /FUNCTION

/INFORMATION/COUNTBLANK.

Figure 129: COUNTBLANK. The example is in the sample file вЂњCount.xls.вЂќ

SUM function

This function sums the values in the data array.

SUM = X1 + X2 +вЂ¦. +Xn

Menu path to function: INSERT / FUNCTION / MATH / SUM.

Figure 130: SUM

Data requirements: This function does not include blank cells or cells with

values that are of the following formats: text, and logical values (that is,

TRUE and FALSE.)

147

Statistical Analysis with Excel

PRODUCT function

This function multiplies all the values referenced.

PRODUCT = X1 * X2 *вЂ¦.* Xn

Figure 131: PRODUCT (multiplying all the values in a range)

Menu path to function: INSERT / FUNCTION / MATH / PRODUCT.

SUMPRODUCT function

This function multiplies corresponding components in two or more data

arrays/ranges, and then sums the results of these multiplications. The

data arrays/ranges must have the same number of data points.

Menu path to function: INSERT /FUNCTION /MATH /SUMPRODUCT

Figure 132: SUMPRODUCT (multiplying individual data points across data series and then

adding up the results of all these multiplications).

Data Array1, data Array2, data Array3 ... are 2 to 30 data arrays/ranges

whose components you desire to multiply and then add. The minimum

number of arrays is two. The data arrays must have the same number of

data points. Non-numeric cell values are assigned the value of zero.

148

Chapter 8: Other Mathematics & Statistics Functions

The X values can be input as references to two or more ranges that may

be nonвЂ“adjacent. The second range should be referenced in the second

text-box вЂњArray2.вЂќ If you use the third text-box, then a fourth text-box

вЂњArray4вЂќ will automatically open. (As you fill the last visible box, another

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

Example

The following formula multiplies all the components of the two data

arrays on the preceding worksheet and then adds the productsвЂ” that is,

3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3.

Note:

Samples will be available at http://www.vjbooks.net/excel/samples.htm.

THE вЂњIFвЂќ COUNTING AND SUMMING FUNCTIONS:

8.2

STATISTICAL FUNCTIONS WITH LOGICAL

CONDITIONS

I display two вЂњif-thenвЂќ two-step functions in this section. The functions

first evaluate a criterion. If a cell in the referenced range satisfies the

criteria then the second part of the function includes this cell.

SUMIF function

This function adds the values in a range if the cell with the value satisfies

a user-defined criterion.

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

вЂў

149

Statistical Analysis with Excel

evaluated.

Figure 133: SUMIF (summing only the cells whose value satisfies one вЂњifвЂќ condition)

вЂў In the box Criteria, enter the condition (a number, expression, or

text) that defines which cells values will be summed. For

example, Criteria can be expressed as 32, вЂњ32,вЂќ вЂњ>32вЂќ.

стр. 42 |