# Count Consecutive Cells

#### chicubs

##### New Member
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>

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### CyrusTheVirus

##### Well-known Member
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

##### Active Member
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>

Last edited:

#### godsaaint

##### Active Member
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>

Last edited:

#### chicubs

##### New Member

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

#### godsaaint

##### Active Member
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

##### Active Member
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

Replies
1
Views
49
Replies
0
Views
63
Replies
5
Views
48
Replies
3
Views
76
Replies
1
Views
70