VBA Code for Frequency Count Trends

[Nonprof]

Hello folks,

I was wondering if someone is able to help me with code for identifying and summarizing trends based on frequency data.
I have been working on this file for a while with the help of somebody else, but he is stuck with regards to reporting the Trend based on data selected.



In the end, I am trying to be able to drop the "Select Date Rage" tab and its code into a number of existing data files.
We report behaviors and their frequencies by day and date. So, the 'data' tab is consistent with all files.


As you can see in the file, we summarized the Data tab on the Select Data Range tab by clicking the Show All Dates button at the top. We are working on the code for summarizing a date range as well.


So, as you can see on the Select Data Range tab there are trend rows at 13, 22, 31.


We are looking for code that can determine the trend based on select all dates or by entering a date range for each behavior as it is shown on the Data tab, and somehow indicating its direction.


Right now, I manually entered up and down arrows to show what I am looking for, but I am ok with any way we can show the trend.
Is this something anyone would be able to help with?

Here is the link to the file.
https://drive.google.com/open?id=1eRwseHWxNHPbEAegvE10oZ_IjGS1LEqq

Thanks in advance! You help is much appreciated!

 

[Rijnsent]

Hi Nonprof,

I don't know your Excel version, but assuming the last one, there are some directions you can take:
TREND and LINEST functions (https://support.office.com/en-us/article/linest-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d ), in VBA: worksheetfunction.LinEst / .Trend -> this tries to fit a line through the data. If Linest returns a negative value, the trend line is dropping. Note: all data has to be numbers, the "-" cells you have right now cause the function to fail.
A second option that comes to mind is to cut your data set into e.g. 2 periods (assuming you find that there is data from row 8 to 100, compare the average of row 8-54 with row 55-100 to see if there is a trend. Looking at your data, there is quite some empty cells, so do build in a "no trend" category in cases of no/low data.
On your data selection:
Have you checked out e.g. COUNTIF, COUNTIFS, SUMIF and SUMIFS? Those formulas do what you're looking for and work with dates too. See e.g. https://exceljet.net/formula/sum-if-date-is-between

Cheers,

Koen