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?


[TABLE="class: grid, width: 1195"]
<tbody>[TR]
[TD]Client Name[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]Process Date[/TD]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]1/2/2019[/TD]
[TD="align: right"]1/3/2019[/TD]
[TD="align: right"]1/4/2019[/TD]
[TD="align: right"]1/5/2019[/TD]
[TD="align: right"]1/6/2019[/TD]
[TD="align: right"]1/7/2019[/TD]
[TD="align: right"]1/8/2019[/TD]
[TD="align: right"]1/9/2019[/TD]
[TD="align: right"]1/10/2019[/TD]
[TD="align: right"]1/11/2019[/TD]
[TD="align: right"]1/12/2019[/TD]
[TD="align: right"]1/13/2019[/TD]
[TD="align: right"]1/14/2019[/TD]
[TD="align: right"]1/15/2019[/TD]
[TD="align: right"]1/16/2019[/TD]
[TD="align: right"]1/17/2019[/TD]
[/TR]
[TR]
[TD]Total Bill Count[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD]Consecutive Trend Increase[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Consecutive Trend Decrease[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.


[TABLE="class: grid, width: 0, align: right"]
<tbody>[TR]
[TD]Client Name
[/TD]
[TD]Process Date
[/TD]
[TD]Total Bill Ct
[/TD]
[TD]Consecutive Trend Increase
[/TD]
[TD]Max Date
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/1/2019
[/TD]
[TD]20
[/TD]
[TD]
[/TD]
[TD]01/09/19 - 01/17/19
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/2/2019
[/TD]
[TD]21
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/3/2019
[/TD]
[TD]22
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/4/2019
[/TD]
[TD]23
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/5/2019
[/TD]
[TD]24
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/6/2019
[/TD]
[TD]6
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/7/2019
[/TD]
[TD]8
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/8/2019
[/TD]
[TD]4
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/9/2019
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/10/2019
[/TD]
[TD]29
[/TD]
[TD]1
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/11/2019
[/TD]
[TD]30
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/12/2019
[/TD]
[TD]31
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/13/2019
[/TD]
[TD]32
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/14/2019
[/TD]
[TD]33
[/TD]
[TD]5
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/15/2019
[/TD]
[TD]34
[/TD]
[TD]6
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/16/2019
[/TD]
[TD]35
[/TD]
[TD]7
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Client A
[/TD]
[TD]1/17/2019
[/TD]
[TD]36
[/TD]
[TD]8
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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!