Microsoft Office Tutorials and References
In Depth Information
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.
function can produce
The SUMSQ function returns the sum of the squares of the arguments.
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