Microsoft Office Tutorials and References
In Depth Information
{=SUM(SMALL(Data,{1,2,3,4,5,6,7,8,9,10}))}
These formulas use an array constant comprising the arguments for the LARGE or SMALL function. If the
value of n for your top- n calculation is large, you may prefer to use the following variation. This formula re-
turns the sum of the top 30 values in the Data range. You can, of course, substitute a different value for 30. Fig-
ure 7-15 shows this array formula in use.
{=SUM(LARGE(Data,ROW(INDIRECT(“1:30”))))}
See Chapter 14 for more information about array constants.
Figure 7-15: Using an array formula to calculate the sum of the 30 largest values in a range.
Conditional Sums Using a Single Criterion
Often, you need to calculate a conditional sum. With a conditional sum, values in a range that meet one or more
conditions are included in the sum. This section presents examples of conditional summing using a single cri-
terion.
Search JabSto ::




Custom Search