Identifying Trends by Date - Countif

shebe228

New Member
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

Well-known Member
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
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
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
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!

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