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.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]10/20[/TD]
[TD]10/27[/TD]
[TD]11/3[/TD]
[TD]11/10[/TD]
[TD]Avg[/TD]
[TD]Consecutive WK Below Avg[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]15[/TD]
[TD]14[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]13[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

What if you had the below scenario?


[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Item[/TD]
[TD]10/20[/TD]
[TD]10/27[/TD]
[TD]11/3[/TD]
[TD]11/10[/TD]
[TD]Avg[/TD]
[TD]Consecutive WK Below Avg[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]15[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]???[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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