Excel Question - Using Offset and Match with only visible columns

Factual Questions
1

Okay, so I have a spreadsheet where Column A always has a value.

Cells B through N may have values (or they may not - B and F might have values, but C D E would not). And this can different for every row in the table. Next row down might have values in A D I M. So the first row is F minus B and the second row is M minus I.

I have a very complex formula in column O that finds the last column that has a value and finds the difference between it and the most ‘recent’ prior cell that has a value like thus:
=IF(N1<>“”,N1-OFFSET(A1,0,MATCH(MAX(A1:M1)+1,A1:M1,1)-1),
IF(M1<>“”,M1-OFFSET(A1,0,MATCH(MAX(A1:L1)+1,A1:L1,1)-1),
IF(L1<>“”,L1-OFFSET(A1,0,MATCH(MAX(A1:K1)+1,A1:K1,1)-1),
IF(K1<>“”,K1-OFFSET(A1,0,MATCH(MAX(A1:J1)+1,A1:J1,1)-1),
IF(J1<>“”,J1-OFFSET(A1,0,MATCH(MAX(A1:I1)+1,A1:I1,1)-1),
IF(I1<>“”,I1-OFFSET(A1,0,MATCH(MAX(A1:H1)+1,A1:H1,1)-1),
IF(H1<>“”,H1-OFFSET(A1,0,MATCH(MAX(A1:G1)+1,A1:G1,1)-1),
IF(G1<>“”,G1-OFFSET(A1,0,MATCH(MAX(A1:F1)+1,A1:F1,1)-1),
IF(F1<>“”,F1-OFFSET(A1,0,MATCH(MAX(A1:E1)+1,A1:E1,1)-1),
IF(E1<>“”,E1-OFFSET(A1,0,MATCH(MAX(A1:D1)+1,A1:D1,1)-1),
IF(D1<>“”,D1-OFFSET(A1,0,MATCH(MAX(A1:C1)+1,A1:C1,1)-1),
IF(C1<>“”,C1-OFFSET(A1,0,MATCH(MAX(A1:B1)+1,A1:B1,1)-1),
IF(B1<>“”,B1-A1,0)))))))))))))

And this works just fine as is (if a little clunky).

What I need though, is for the above to work exactly as it does now, but only on visible columns, so that if I hide column I, the second row above would become M minus D. I really want to avoid VBA since this is a large data set and VBA functions really cripple the sheet.

Any help out there?

2

It’s hard to get the visibility status of a column without VBA. Instead, can you use a simple cell to indicate whether the column should be included in the calculations? Then you can simplify the rest of the formula, I think.

Here’s a working online example or just a screenshot.

To break it down:

  • A simple TRUE/FALSE cell in row 3 indicates whether that column should be included in calculations. Instead of using show/hide columns, you can use conditional formatting to indicate whether that column is “hidden”.
  • We can use =FILTER(B5:G5, (B5:G5<>"") to get all the non-blank cells
    • We combine that with * (B$3:G$3<>TRUE) to exclude the “hidden” columns from row 3. (The * operator combines filters, and the whole filter formula becomes =FILTER(B5:G5, (B5:G5<>"") * (B$3:G$3<>TRUE)))
  • Then we use =TAKE(data, 1, -2) to get the last two values of that filtered output. That’s what the 1, -2 parameters do (first row, last two values starting from the end).
  • We save that =TAKE() output into a variable called LastTwoNonEmptyNotHiddenValues.
  • We subtract the second-to-last LastTwoNonEmptyNotHiddenValues from the last LastTwoNonEmptyNotHiddenValues using INDEX().
3

With a few modifications that worked perfectly!

The issue that the columns actually ARE hidden and it’s done by VBA, I just had to add code to modify the column flag during the show/hide process and modify the formula to deal with situations where either only one column had a value or only one column was showing.

Thanks tons!

4

Great! And welcome :slight_smile:

5

TAKE is something I’m definitely going to have to look into more.