Search backward in range till empty cell

[fedude]

I manage a league and I have a large spreadsheet of scores for each player in the league. Each player has a row and every week I record their score in the next column in that row.

What I want to do is find out the current "attendance" streak for each player in the league. In other words how many current weeks in a row has each player showed up since they last missed a week - could be zero, could be 30.

So I need a routine/formula to loop through a range- backwards, and count the number of non-empty cells until it reaches an empty cell and then report the count. I assume this requires VBA but I could not figure out how to loop through a range (row) backwards looking for an empty cell.

I've attached a sample spreadsheet

[XOR LX]

Hi,

One of many possible solutions, I'm sure. This needs to be entered as an array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER). In cell EC9 and copy down as required:

=MAX(COLUMN($C9:$DW9))-MAX(IF($C9:$DW9="",COLUMN($C9:$DW9)))

Regards

[fedude]

WOW. Great answer! Many Thanks!

If I understand this formula correctly, it looks for the count of the last empty cell then subtracts that from the count of all the cells. So there is no reason to loop backwards through the cells. Very nice!!

[XOR LX]

You're welcome!

[aloha31]

Note that this solution only finds the current streak, and not the player's maximum overall streak. Great answer though!

[Teethless mama]

Try "Non Array" formula

EC9: =LOOKUP(1E+99,COLUMN(C9:DW9))-LOOKUP(2,1/(C9:DW9=""),COLUMN(C9:DW9))

Normally Enter. Copy down as far as needed.

[fedude]

Thanks Teethless. Works perfectly.

I can't seem to get my head around your solution. Can you provide a little explanation of how this works?

[fedude]

XOR,

I love your formula - I want the last streak just as your formula gives.

I was wondering if you could expand this just a bit. MY problem is that the range of data to apply your formula is in the middle of a giant worksheet and the last column I want to search changes each week.

So I need to bound your formula (now bounded by $DW$9) with the last used column in a range within lots of other stuff. I assume I need the offset function and I've made some progress:

I did figure out how to find the last used column within my range:

=MAX(COLUMN($D$4:$AM$8)*($D$4:$AM$8<>""))-COLUMN($D$4)+1

But I can't figure out how to apply this to your formula

[Spreadsheet Attached]