Count Consecutive Cells

[chicubs]

I am trying to count the current consecutive weeks an item is below its average for a given period of time starting with the past week. Not sure how to do this. Item 123 should have an output of 3 since it has been below the average the past 3 weeks. Item 1234 should have an output of 0 since it was above the average last week.

Item	10/20	10/27	11/3	11/10	Avg	Consecutive WK Below Avg
123	15	8	7	6	9	???
1234	15	14	10	15	13	???

<tbody>
</tbody>

 

[CyrusTheVirus]

What if you had the below scenario?

Item	10/20	10/27	11/3	11/10	Avg	Consecutive WK Below Avg
12345	15	8	10	6	9	???

<tbody>
</tbody>

 

[godsaaint]

Assuming "Item" is in A1, This should work: =COLUMN(E2)-IF(B2:E2<F2,1,LARGE(IF(B2:e2>=F2,COLUMN(B2:E2)),1))<F2,1,LARGE(IF(B2:e2>

EDIT: corrected a mistake with the formula.

=COLUMN(E2)-IF(B2:E2<F2,1,LARGE(IF(B2:E2>=F2,COLUMN(B2:E2)),1))</F2,1,LARGE(IF(B2:e2></F2,1,LARGE(IF(B2:e2>

 

[godsaaint]

Assuming "Item" is in A1, This should work: =COLUMN(E2)-IF(B2:E2<F2,1,LARGE(IF(B2:e2>=F2,COLUMN(B2:E2)),1))<F2,1,LARGE(IF(B2:e2>

EDIT: corrected a mistake with the formula.

=COLUMN(E2)-IF(B2:E2<F2,1,LARGE(IF(B2:e2>=F2,COLUMN(B2:E2)),1))</F2,1,LARGE(IF(B2:e2></F2,1,LARGE(IF(B2:e2>

Use this one: =COLUMN(E2)-IF(B2:E2 < F2,1,LARGE(IF(B2:E2>=F2,COLUMN(B2:E2)),1))

delete the spaces before and after "<"


</F2,1,LARGE(IF(B2:e2>

 

[chicubs]

Thanks! Conversely, what formula would work to count the consecutive weeks ABOVE the average?

 

[godsaaint]

Forgot to tell you its an array formula (CRTL-SHIFT-ENTER), though I guess you figured it out.

Thanks! Conversely, what formula would work to count the consecutive weeks ABOVE the average?

I'll get back to you as soon as I can,might be tomorrow morning

 

[godsaaint]

Thanks! Conversely, what formula would work to count the consecutive weeks ABOVE the average?

Hey chicubs, did some more testing with the last formula I gave you, it might seems like it works but it has some weaknesses/mistakes. Sorry for it, these should be without failure:

Consecutives weeks below average: =IFERROR(COLUMN(E2)-LARGE(IF(B2:E2>=F2,COLUMN(B2:E2)),1),COUNTA(B2:E2))
Consecutives weeks above average: =IFERROR(COLUMN(E2)-LARGE(IF(B2:E2 < F2,COLUMN(B2:E2)),1),COUNTA(B2:E2))

Confirm both with CTRL-SHIFT-ENTER