Counting Frequency of numbers & identifying another set of numbers in same row

I tried this in access but cannot find an answer so I'm hoping that perhaps it might be easier in excel.

I’m hoping someone can help me. I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zero’s and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zero’s preceding it identify that person.

For example Joe would be identified below because he had 6 consecutive zero’s and then he had 5 weeks of numbers immediately following the string of zero’s. Bob would not be identified because he had 5 consecutive zero’s and then 5 sets of numbers immediately following the string of zero’s so the zero frequency isn’t higher than the number frequency immediately following.

I hope this makes sense – any help would be greatly appreciated.

I have attached a sample of the table I am referencing above.Book2.xlsx

ive not tested it fully but I think this function will do it.....

Please Login or Register  to view this content.

Thanks for the tester, its had my brain frazzled with different combinations of this and that. Its been fun!

Thanks - I can't wait to try it!

unfortunately, this is not working...