Count Consecutive Cells Above/Below a Value

[chicubs]

I am trying to count the consecutive weeks an item is below its average for a given period of time. Not sure how to do this. Item 123 should have an output of 3 and item 1234 should have an output of 1.

[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]15[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]???[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D	E	F	G
1	Item	10/20/2016	10/27/2016	11/3/2016	11/10/2016	Avg	Consecutive WK Below Avg
2	123	15	8	7	6	9	3
3	1234	15	14	15	8	13	1
Sheet1

Cell Formulas
Range	Formula
G2	{=MAX(FREQUENCY(IF(B2:E2B2:E2)),IF(B2:E2>=F2,COLUMN(B2:E2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Source: http://www.mrexcel.com/forum/excel-questions/550976-counting-consecutive-cells-value.html

 

[chicubs]

That works for items where Column E is a value less than the average. However, it does not return a 0 for items where Column E is above the average. See the link below for the example.

Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[chicubs]

Try this link: Excel Issue - Album on Imgur

 

[63falcondude]

That works for items where Column E is a value less than the average. However, it does not return a 0 for items where Column E is above the average. See the link below for the example.

I'm not sure what you mean. I took the example that you posted in post #4 and it seems to work fine. It results in the maximum number of consecutive weeks where the number is higher than the average. Is this not what you are looking for?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1		9/17/2016	9/24/2016	10/1/2016	10/8/2016	10/15/2016	10/22/2016	10/29/2016	11/5/2016
2	Item	Sales Units	Sales Units	Sales Units	Sales Units	Sales Units	Sales Units	Sales Units	Sales Units	Avg Sales Units	Consecutive WK Below Avg
3	203335	288	299	234	220	239	194	188	178	248	6
4	200798	129	128	127	142	143	147	151	150	133	3
Sheet1

Cell Formulas
Range	Formula
K3	{=MAX(FREQUENCY(IF(B3:I3B3:I3)),IF(B3:I3>=J3,COLUMN(B3:I3))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chicubs]

This is what I'm looking for! What formula would I use if I wanted to count the consecutive weeks ABOVE the average?

 

[63falcondude]

This is what I'm looking for! What formula would I use if I wanted to count the consecutive weeks ABOVE the average?

Just reverse the inequalities. < becomes > and >= becomes <=.

Formula would then become

=MAX(FREQUENCY(IF(B3:I3>J3,COLUMN(B3:I3)),IF(B3:I3<=J3,COLUMN(B3:I3)))) Ctrl Shift Enter