I have a data sample with days as columns (20180801, 20180802, etc) and IDs as rows. Whenever a certain ID performs an action on a certain day, a '1' is marked under the right cell (eg: if ID 200 performs an action on 20180920, a '1' will show under row(ID 200) and column(20180920).
I need to figure out the average days in between actions over a certain period X (variable, in # of days). So if X is 30, the formula would look at the average frequency of 'actions' (ie: in days) over the past 30 days. If X is 60, over the past 60 days, etc.
See sample under Additional Project Files. I'm looking to fill C10:C2033 with a formula that would check, over the last 30 days from a certain date (CELL B7) (eg: from 20180924,), the average days in between actions. So if someone performs an action on the 10th, 15th, and 20th then the average would be 5 days. However if someone performs it on the 11th, 12th, 13th, 14th, 15th and then stops the average days in between actions would be 1 day (takes the last action into account and not just the last day) - generally speaking, I would do this by adding an intermediary step that splits every client ID (eg: ID 3) and lists all of the actions (Action1, Action2, Action3) - gets the date difference between each order (Date(Action2) - Date(Action1), Date(Action3) - Date(Action2), etc.) and then calculates the Average Action Frequency for each ID.
Then I need to compare the date difference between the a certain date (eg: 20180924) and the ‘last’ date he completed an action. If this is greater than 2 times the avg order frequency, I want to categorize as it some “Greater than 2 times”. If between 1 and 2 times the average order frequency, categorize as “between 1 and 2”. And if less, categorize as “Less than 1”
Hello sir Welcome in my profile appreciate if you contact me to see how can work on your project, I am expert in excel and appreciate to visit my profile and see reviews Thank