Identifying Trends by Date - Countif

[shebe228]

I have an extremely large data set by day for the last 6 months. I want to count the number of consecutive days where there was an increase in bill volume for the purpose of identifying the largest streak.

For example, we continuously increased bill counts from January 1st through January 5th I want to return 5 days. There was another upward trend over consecutive days from January 10th through January 17th, I want to return 7 days. The ultimate goal is to identify what date range had the most consecutive days with a trend of increasing bill counts.

Additionally, I want to calculate the reverse to identify how many consecutive days we had a trend of decreasing bill count.

Does anybody have any suggestions on the most efficient way to calculate this?

Client Name

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Client A

Process Date

1/1/2019

1/2/2019

1/3/2019

1/4/2019

1/5/2019

1/6/2019

1/7/2019

1/8/2019

1/9/2019

1/10/2019

1/11/2019

1/12/2019

1/13/2019

1/14/2019

1/15/2019

1/16/2019

1/17/2019

Total Bill Count

20

21

22

23

24

6

8

4

2

29

30

31

32

33

34

35

36

Consecutive Trend Increase

Consecutive Trend Decrease

<tbody>
</tbody>

 

[DRSteele]

Identifying streaks is a challenging operation in Excel. Please review this thread and see if it sparks any ideas for you. I tried doing this with function COUNTIFS but it bogged down the CPU, so I asked for help and discovered that function OFFSET is far superior.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.mrexcel.com/forum/excel-questions/1100588-streaks-maximum-streaks-la.html[/FONT]

 

[shebe228]

I figured it out! I transposed rows and columns and used the following formulas:

Consecutive Trend Increase: =IF(IF(C11>C10,1,0)>0,IF(C11>C10,1,0)+D10,0)

Max Date: =(TEXT(((MAX(D10:D27,B9:B27))-(MAX(D10:D27,D10:D27))),"mm/dd/yy"))&" - "&(TEXT((MAX(D10:D27,B9:B27)),"mm/dd/yy"))


I'm sure I could imbed those two formulas, but I didn't want to push my luck.

Client Name	Process Date	Total Bill Ct	Consecutive Trend Increase	Max Date
Client A	1/1/2019	20		01/09/19 - 01/17/19
Client A	1/2/2019	21	1
Client A	1/3/2019	22	2
Client A	1/4/2019	23	3
Client A	1/5/2019	24	4
Client A	1/6/2019	6	0
Client A	1/7/2019	8	1
Client A	1/8/2019	4	0
Client A	1/9/2019	2	0
Client A	1/10/2019	29	1
Client A	1/11/2019	30	2
Client A	1/12/2019	31	3
Client A	1/13/2019	32	4
Client A	1/14/2019	33	5
Client A	1/15/2019	34	6
Client A	1/16/2019	35	7
Client A	1/17/2019	36	8

<tbody>
</tbody>

 

[DRSteele]

I'm glad you got something workable. Keep in mind there may be more than one date for each consecutive streak. There might be several times that a streak is four, or three or whatever.

 

[shebe228]

I'm glad you got something workable. Keep in mind there may be more than one date for each consecutive streak. There might be several times that a streak is four, or three or whatever.

Yes, we just discussed that. I think this gives a starting point and at least gives the max range and then they can go back with a CTRL + F to see if there may be additional criteria that fit.

Thank you!