Arrays as an argument in an Excel formula

Most of the Excel functions that take an array as an argument don’t require that this array be a cell range. E.g. the formula =STDEV.S(A1:A10) will return the standard deviation of the values in cell range A1:A10.  You can also use the array formula =STDEV.S(A1:A10-B1:B10). This will return the standard deviation of the pairwise differences between the cells in A1:A10 and B1:B10. Thus, although STDEV.S is not an array function, the fact that the array formula =A1:A10-B1:B10 is used as an argument makes =STDEV.S(A1:A10-B1:B10) an array formula.

Another example is =SUM(MINVERSE(A1:C3)). This array formula finds the inverse of the matrix A1:C3 and then sums up the 9 values in this matrix.

The following Excel functions are an exception, in that they only take arguments that are a cell range and don’t permit combinations such as those described above: COUNTIF, COUNTIFS, SUMIF, SUMIFS, MAXIFS, MINIFS, AVERAGEIF, AVERAGEIFS, RANK, RANK.EQ and RANK.AVG.

Thus, we cannot create array formulas such as =RANK(A1,TRANSPOSE(A1:A10)) or =COUNTIF(A1:A10*2, 5).

Note too that formulas such as =AVERAGE(A1:A10) and =AVERAGE(A1:A10*1) yield the same value, as expected, at least when range A1:A10 contains only numeric data. But while =AVERAGE(A1:A10) outputs the mean of A1:A10 ignoring empty cells and cells containing alphanumeric data, =AVERAGE(A1:A10*1) does not produce the correct value when A1:A10 contains non-numeric cells (since blank cells are treated as zero in this case).

Leave a Comment