Microsoft Office Tutorials and References

In Depth Information

To sum a different number of values, replace the 10 in the argument for the INDIRECT function with another

value.

If the number of cells to sum is contained in cell C17, use the following array formula, which uses the concaten-

ation operator (&) to create the range address for the INDIRECT function:

{=SUM(LARGE(Data,ROW(INDIRECT(“1:”&C17))))}

To sum the
n smallest
values in a range, use the SMALL function instead of the LARGE function.

Computing an average that excludes zeros

Figure 15-2 shows a simple worksheet that calculates average sales. The formula in cell C13 is

=AVERAGE(B4:B11)

Figure 15-2:
The calculated average includes cells that contain a 0.

Two of the sales staff had the week off, however, so including their 0 sales in the calculated average doesn't ac-

curately describe the average sales per representative.