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.

Ignoreblank1

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. 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.

Leave a Reply

Your email address will not be published.