Microsoft Office Tutorials and References

In Depth Information

**SUMSQ**

In the example shown in Figure 10.27, the
SUMPRODUCT
function is used to construct a rating

system for employees. The table in the range C6:H10 provides a set of weightings for

employee qualities based on their job type. To allow comparison across job types, the sum of

the weightings must be equal to 1. Each row range of weightings has been given a name by

highlighting C6:H10 and selecting Insert, Name, Create from the menu and checking the box

for Create names in the left column and then pressing Enter. The employees are then given a

ranking for each quality in the table B13:H22. The formula shown in cell I22 uses the

SUMPRODUCT
function to apply the correct weighting for the person in cell B22 by using the

INDIRECT
function with the job type name in C11 as the second array argument, which

returns the named range SectionHead (D6:H6). This formula was filled down to I19 to return

the weighted rating for each person, based on their job type.

The
ARRAY
arguments are 2 to 30 arrays, the components of which

you want to multiply and then add.

ARRAY

Figure 10.27

The
SUMPRODUCT

function can produce

weighted employee

ratings.

SUMSQ

The
SUMSQ
function returns the sum of the squares of the arguments.

=SUMSQ(number1,number2, ...)

For example:
SUMSQ(2,3)
equals 13.

An array formula of the form
=SUM(A1:C3^n)
provides a general method of sum of a series of

numbers raised to a power n. You can enter an array formula by holding down the Ctrl+Shift

while pressing Enter.

NUMBER 1, NUMBER 2,...
The
NUMBER
arguments are 1 to 30 arguments for which you

want the sum of the squares. You also can use a single array

or a reference to an array instead of arguments separated

by commas.