Count Consecutive Cells

chicubs

New Member
Joined
Aug 10, 2016
Messages
8
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.

Item10/2010/2711/311/10AvgConsecutive WK Below Avg
123158769???
12341514101513???

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Jan 28, 2015
Messages
736
Office Version
  1. 365
Platform
  1. Windows
What if you had the below scenario?


Item10/2010/2711/311/10AvgConsecutive WK Below Avg
123451581069???

<tbody>
</tbody>
 

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
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
Joined
Sep 16, 2016
Messages
285
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
Joined
Aug 10, 2016
Messages
8

ADVERTISEMENT

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

godsaaint

Active Member
Joined
Sep 16, 2016
Messages
285
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
Joined
Sep 16, 2016
Messages
285
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,006
Messages
5,526,244
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top