# Ignore Blanks in a Formula

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.

Posted in Uncategorized

## One thought on “Ignore Blanks in a Formula”

1. Frank Kabel says:

Hi Dick
my alternative (also array entered):
(LOOKUP(2,1/(A1:J1<>””),A1:J1)/INDEX(A1:J1,MATCH(TRUE,A1:J1<>””,0)))^(1/COUNTA(A2:J2))-1

Frank

Posting code? Use <pre> tags for VBA and <code> tags for inline.