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

Board Regular
Joined
Jan 18, 2017
Messages
244
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

Board Regular
Joined
May 25, 2017
Messages
76
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:
Code:
=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):
Code:
=MIN(IF(INDIRECT("A"&G4&":E"&G4)="",COLUMN(INDIRECT("A"&G4&":E"&G4))))
 
Last edited:

Forum statistics

Threads
1,078,213
Messages
5,338,891
Members
399,265
Latest member
aj17x55

Some videos you may like

This Week's Hot Topics

Top