Office-it.orG

Sum a range that contains error values

The SUM function in Microsoft Excel doesn’t work when you try to sum a range that contains an error value such as #N/A. This example shows you how to sum the values in a range named “Data” that contains errors:

=SUM(IF(ISERROR(Data),"",Data))

The formula creates a new array that contains the original values minus any error values. Starting from the inner functions and working outward, the ISERROR function searches the cell range (Data) for errors. The IF function returns one value if a condition you specify evaluates to TRUE, and another value if it evaluates to FALSE. In this case, it returns empty strings (“”) for all error values because they evaluate to TRUE, and the remaining values from the range (Data again) because they evaluate to FALSE, meaning that they don’t contain error values. The SUM function then calculates the total for the filtered array.

Count the number of error values in a range

This example is similar to the previous formula, but it returns the number of error values in a range named “Data” instead of filtering them out:

=SUM(IF(ISERROR(Data),1,0))

This formula creates an array of 1s for the cells that contain errors, and 0s for the cells that don’t. You can simplify the formula and achieve the same result by removing the third argument for the IF function, like so:

=SUM(IF(ISERROR(Data),1))

If you don’t specify the argument, the function returns FALSE if a cell does not contain an error value. You can simplify the formula even more:

=SUM(IF(ISERROR(Data)*1))

This version works because TRUE*1=1 and FALSE*1=0.

Sum values based on conditions

You often need to sum values based on conditions. For example, this array formula sums just the positive integers in a range named “Sales”:

=SUM(IF(Sales>0,Sales))

The IF function creates an array of positive values and false values. The SUM function essentially ignores the false values because 0+0=0. The cell range that you use in this formula can consist of any number of rows and columns.

You can also sum values that meet more than one condition. For example, this array formula calculates values greater than zero and less than or equal to five:

=SUM((Sales>0)*(Sales< =5)*(Sales))

Keep in mind that this formula returns an error if the range contains one or more non-numeric cells.

You can also create array formulas that use a type of OR condition. For example, you can sum values that are less than 5 and greater than 15:

=SUM(IF((Sales<5)+(Sales>15),Sales))

The IF function finds all values smaller than 5 and greater than 15 and then passes those values to the SUM function.

Important You can’t use the AND and OR functions in array formulas directly. Why not? Those functions return a single result, either TRUE or FALSE, and array functions require arrays of results. You can work around the problem by using the logic shown in the previous formula. In other words, you perform math operations such as addition or multiplication on values that meet the OR or AND condition.

Compute an average that excludes zeros

This example shows you how to remove zeros from a range when you need to average the values in that range. The formula uses a data range named “Sales”:

=AVERAGE(IF(Sales<>0,Sales))

The IF function creates an array of values that do not equal zero and then passes those values to the AVERAGE function.

Count the number of differences between two ranges of cells

This array formula compares the values in two ranges of cells named “MyData” and “YourData” and returns the number of differences between the two. If the contents of the two ranges are identical, the formula returns 0. To use this formula, the cell ranges must be the same size and of the same dimension:

=SUM(IF(MyData=YourData,0,1))

The formula creates a new array of the same size as the ranges that you’re comparing. The IF function fills the array with 0s and 1s (0s for mismatches and 1s for identical cells). The SUM function then returns the sum of the values in the array.

You can simplify the formula like this:

=SUM(1*(MyData<>YourData))

Like the formula that counts error values in a range, this formula works because TRUE*1=1, and FALSE*1=0.

Find the location of the maximum value in a range

This array formula returns the row number of the maximum value in a single-column range named “Data”:

=MIN(IF(Data=MAX(Data),ROW(Data),""))

The IF function creates a new array that corresponds to the “Data” range. If a corresponding cell contains the maximum value in the range, the array contains the row number. Otherwise, the array contains an empty string (“”). The MIN function uses the new array as its second argument and returns the smallest value, which corresponds to the row number of the maximum value in “Data.” If the “Data” range contains identical maximum values, the formula returns the row of the first value.

If you want to return the actual cell address of a maximum value, use this formula:

=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data),"")),COLUMN(Data))

Source: http://office.microsoft.com/en-us/excel/HA010872271033.aspx

Popularity: 6% [?]