Is it possible to find first non blank cell in a range and return the column that it's in as part of a formula build?

[Lewzerrrr]

Hi,

I have a data source that I need to use basically the following formula, =AVERAGE(IF(MOD(COLUMN(G4:CD4)-COLUMN(G4)+1,4)=1,G4:CD4,"")) confirmed with CSE.

I need it to adjust the first cell by finding the first non blank cell in that G4:CD4 range.. the one below I manually adjusted to =AVERAGE(IF(MOD(COLUMN(O5:CD5)-COLUMN(O5)+1,4)=1,O5:CD5,""))

Thanks,

 

[NickRed18]

Here is an example 10x5 matrix with a blank cell missing at Row 5, Column 3 (C5):

1 11 21 31 41
2 12 22 32 42
3 13 23 33 43
4 14 24 34 44
5 15 __ 35 45
6 16 26 36 46
7 17 27 37 47
8 18 28 38 48
9 19 29 39 49
10 20 30 40 50


Next is my formula to get the row where the first blank occurs (going from left to right, top to bottom), and this formula is located in my G4 cell, so you will need to make adjustments depending on where yours is. Also, it is an array function, so remember to enter it in with Ctrl+Shift+Enter:

=MIN(IF($A$1:$E$10="",ROW($A$1:$E$10)-ROW($A$1)+1))

Last is the column reference number. It's in my cell G5, so once again make adjustments (and it is also an array function):

=MIN(IF(INDIRECT("A"&G4&":E"&G4)="",COLUMN(INDIRECT("A"&G4&":E"&G4))))