Hi everyone,

I had a rather intriguing excel question for the forum.

My basic idea is to find the most number of days a person has been scoring lesser than 1 and of course it resets if he/she able to get 1 or above on a certain day. Therefore each value on a row is per day basis. (column means dates)

I also want blanks to be considered as 0.

Help me out, Please. <3

real example: # 0.1 0.2 1 1.1 2 is the first row where '#' is blank, anything less than 1 should be counted and the first occurrence of 1 or above value resets the count and counter starts again from that spot onwards, while ignoring all before it.

# # 1.1 2 --> gives 2
0.1 # 0.2 2 --> gives 3
## 0.1 1.1 0.1 --> gives 1
if no other choice then I can replace all blanks with '0' too coz the minimum score is 0.1 and maximum is 2.(possible values being 0.1, 0.2, 1, 1.1, 2)

NOTE: I am currently using OneDrive sheets (Web version), so please check if your answer works there too.

my first row is D4:AH4

GeoffW28 had helped me on a previous post regarding the same topic but had to close that coz it changed from the initial description. His solution goes like this.

=IFERROR(COUNT($C8:$W8) - LOOKUP(2,1/($C8:$W8>=1), COLUMN($C8:$W8)-2), COUNT($C8:$W8))
Put the formula in C8 and your numbers in row-8 starting at col-C.
The above solution he suggested implies that I replace all blanks with '0' since the minimum value is 0.1, '0' has no other meaning. So his solution works perfectly for my scenario but only in MS Excel. I tried the solution on onedrive sheets and its not accepting the formula.

So you guys can either work on the above solution to make it acceptable on onedrive or give me an alternative.

Thank you so much if you made it this far and I really appreciate the time and effort anyone puts into this. Peace!!