Microsoft Office Tutorials and References
In Depth Information
The AGGREGATE function, which works only in Excel 2010 and later, provides another
way to sum a range that contains one or more error values. Here's an example:
=AGGREGATE(9,2,D4:D10)
The first argument, 9, is the code for SUM. The second argument, 2, is the code for “ignore error values.”
Counting the number of error values in a range
The following array formula is similar to the previous example, but it returns a count of the number of error val-
ues in a range named Data :
{=SUM(IF(ISERROR(Data),1,0))}
This formula creates an array that consists of 1s (if the corresponding cell contains an error) and 0s (if the cor-
responding cell does not contain an error value).
You can simplify the formula a bit by removing the third argument for the IF function. If this argument isn't spe-
cified, the IF function returns FALSE if the condition is not satisfied (that is, the cell does not contain an error
value). In this context, Excel treats FALSE as a 0 value. The array formula shown here performs exactly like the
previous formula, but it doesn't use the third argument for the IF function:
{=SUM(IF(ISERROR(Data),1))}
Actually, you can simplify the formula even more:
{=SUM(ISERROR(Data)*1)}
This version of the formula relies on the fact that:
TRUE * 1 = 1
and
FALSE * 1 = 0
Summing the n largest values in a range
The following array formula returns the sum of the ten largest values in a range named Data :
{=SUM(LARGE(Data,ROW(INDIRECT(“1:10”))))}
The LARGE function is evaluated ten times, each time with a different second argument (1, 2, 3, and so on up
to 10). The results of these calculations are stored in a new array, and that array is used as the argument for the
SUM function.
Search JabSto ::

Custom Search