Identifying Trends by Date - Countif

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
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 NameClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient AClient A
Process Date1/1/20191/2/20191/3/20191/4/20191/5/20191/6/20191/7/20191/8/20191/9/20191/10/20191/11/20191/12/20191/13/20191/14/20191/15/20191/16/20191/17/2019
Total Bill Count202122232468422930313233343536
Consecutive Trend Increase
Consecutive Trend Decrease

<tbody>
</tbody>
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,052
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 28, 2017
Messages
46
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

Well-known Member
Joined
Mar 31, 2015
Messages
2,052
Office Version
365
Platform
Windows
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.
 
Last edited:

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
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!
 

Forum statistics

Threads
1,082,589
Messages
5,366,494
Members
400,896
Latest member
Scab

Some videos you may like

This Week's Hot Topics

Top