Daily Dose of Excel

CP sent me an interesting problem. He has rows of data spanning columns A:J, but some of the data is missing, that is, it doesn’t always start at A and it doesn’t always end at J. If all the data was there, the formula would look like this:

=((J1/A1)^(1/10)-1)

However, J1 and A1 need to be adjusted to the actual data and the 1/10 needs to be 1/x where x is the number of data points.

Here’s what I came up with, although it’s not pretty:

=((SUM((A2:J2)*(COLUMN(A2:J2)=MAX((COLUMN(A2:J2))*(A2:J2<>“”))))/
   SUM((A2:J2)*(COLUMN(A2:J2)=MIN(IF(A2:J2=”",”",(COLUMN(A2:J2))*(A2:J2<>“”))))))^
   (1/COUNTA(A2:J2))-1)

This array formula (enter with Control+Shift+Enter) seems to give the correct answer. Here’s how it breaks down:

(COLUMN(A2:J2)=MAX((COLUMN(A2:J2))*(A2:J2<>“”))))

This gets the value in A2:J2 where the column is the largest column that isn’t blank. It maps to J1 from the above formula.

)*(COLUMN(A2:J2)=MIN(IF(A2:J2=”",”",(COLUMN(A2:J2))*(A2:J2<>“”)))))

This gets the value from A2:J2 where the column is the smallest column that isn’t blank. It maps to A1 from the above formula.

A2:J2))

This counts the non-blank entries in the range. Assuming that there are no blank cells in the middle of the data, COUNTA provides the correct denominator. COUNT would work too, it’s just a habit of mine to use COUNTA.

Finally, the results of my extensive testing are below.

Surely this formula can be shortened. That’s your homework for the weekend.