Hi all,
I browsed through the forum but couldn't find this one. Looking to find the longest "streak" of each row. In other words, the number of consecutive columns in the same row that have a value. For example
Row 1: Column A- [blank] / Column B- [number] / Column C- [number] / Column D- [number] / Column E- [blank]
So streak equals 3
Appreciate it!
are you still using MAC 2011?
<----- If you are happy with your solution please click on the "* Add Reputation" as a way to say thank you.
This should work for you:
=MAX(FREQUENCY(IF(A1:E1<>"",COLUMN(A1:E1)),IF(A1:E1<>"",0,COLUMN(A1:E1))))
If you're using Office 2011 for Mac, I think you'd need to use CTRL+SHIFT+ENTER to confirm, rather than just ENTER. (Newer versions of Office don't require CSE for array formulas.)
(Example seen here: https://exceljet.net/formula/longest-winning-streak)
Thank you Paul, worked like a charm. And yes, I need to update the version. I'm on a 365 subscription now!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks