Excel – the cleanest way to write an array formula to facilitate the addition of more rows

While I haven't been able to think up a single formula solution (maybe someone else will!), I did come up with something that takes up a lot less spreadsheet real estate than another 150 x 360 matrix.

The basic idea is to compute the cumulative totals in each row for one column of data and then use that in a data table ("what-if analysis") to generate the counts for all of the columns.

Starting point is the column of computations for the rows in a single data column.

As shown in the screenshot below, I set up a worksheet with 10 columns of data.

Helper Column

To the right of the data, I've set up the helper column L.

Cell L1 contains the COUNTIF of the rows in that column that have a sum greater than zero.

For the row sums, instead of a simple sum of the columns in each row (again, just for column A) I use a sum of the range returned by the OFFSET function. This function has the form

OFFSET(reference cell, number of rows to offset, number of columns to offset, 
       height of range to return, width of range to return)

Cell L3 has the first of the SUM(OFFSET(...)) expressions. It calculates the row sum for the range that is 0 rows down from cell A2 and 0 columns to right, with a height of 1 row and a width equal to the value in cell L2. In this case, L2 has the value of 1.

This formula is copied down through 360 rows, in each case computing the sum of a range 1 row high and with a width determined by the value in cell L2.

For exampe, if the value in L2 were changed to 2, then the formulas in the column would calculate the the row-wise sums of the values in columns A & B for each of the 360 rows. And cell L1 would show the number of rows in the range A2:B361 with a sum greater than 0.

Data Table

Excel's data table functionality makes it possible to quickly determine the impact on a calculation of varying the value of one (or two) of the inputs to that calculation. It is set up via the What-If Analysis button in the Data Tools section of the Data tab on the ribbon.

The accompanying picture shows the data table setup.

The data table will be created in the range R1:S10. At the top of the table, in cell S1 is the result cell for which the inputs will be varied. In this case, the result cell holds the formula =L1, which is just a reference to the COUNTIF formula at the top of the helper column L.

I pre-entered the "what-if" values in cells R2:R10. The values shown - 1, 2, ... , 9 - represent the widths of the ranges that the OFFSET will return. And the "column input cell" is the cell L1, the cell that determines the width of the rows that are summed in the helper column.

In a nutshell, we feed in the widths 1-9 (equivalent to columns "A", "A:B", "A:C", etc.) and the data table computes the number of rows that have sums greater than 0 for each of those column spans.

The last picture shows the final results. The data table has calculated the row counts for each column of the input data, i.e., the counts of the row-wise sums (of the previous columns) that are greater than 0. Those counts were returned in cells S2:S10 of the data table. I transferred the counts to the first row of the original data using the TRANSPOSE function.

The example worksheet with all the calculations is available here.

Sadly, the use of the volatile INDIRECT and OFFSET are almost ubiquitously recommended in such cases on the various Excel sites around, even though there exists a perfectly good, minimally volatile (in fact, volatile "at workbook open" only, as I understand) set-up using INDEX.

To determine the last non-empty row in a column, column A say, it will be useful to know the datatype of the entries within that column, and also whether there are any null strings ("") present.

Although there are set-ups available which will work whatever the datatype of the entries in question, they are necessarily of a type such that they must process each element within the range passed to them. As such - and particularly if the range over which it is necessary to calculate is quite large - they can be extremely resource-heavy (the second suggestion by kyle being a good example, having to process more than a million cells, irrespective of whether the last non-empty cell is in row 1 or row 1048576).

It is also worthwhile storing the value of the last non-empty row as a Defined Name, LRow say.

The possible definitions for LRow are then as follows:

1) If there are no null strings and all entries are non-numerical, you can define LRow as:

=MATCH("Ω",A:A)

2) If there are no null strings and all entries are numerical, you can define LRow as:

=MATCH(9.9E+307,A:A)

3) If there are no null strings and entries are of a mixed datatype, i.e. some numerical, some non-numerical, you can define LRow as:

=MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))

If there are null strings within the range then the solutions become necessarily more complex, and it is no longer possible to avoid a construction in which each cell within the array is processed individually. As such, the best we can do is to minimize the range which we pass to be processed.

Hence, we have:

4) If there are null strings, you can define LRow as:

=MATCH(1,0/(LEN(A1:INDEX(A:A,MAX(MATCH("Ω",A:A),MATCH(9.9E+307,A:A))))>0))

(You could of course make this even more efficient if the datatype is consistently numerical or non-numerical, as above.)

which requires array-entry (CSE) and which, in general, will vastly reduce the range being processed, especially compared to something such as:

=MATCH(1,0/(LEN(A:A)>0))

which is disastrous in terms of efficiency.

Note that the additional functions within the former are of very little detriment: it is far more important to reduce the number of cells being processed than to worry about an additional function call or two.

You can now use this within an INDEX construction to dynamically define your range. For example:

=A1:INDEX(A:A,LRow)

which, as mentioned, is barely volatile at all.

Regards