Excel Formula - Calculate average days between actions (frequency of actions) in terms of days

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”

Beceriler: Veri İşleme, Excel, Matematik, Visual Basic

Daha fazlasını gör: call web service excel formula, excel formula sheet, microsoft excel formula sheet, excel slow calculating 4 processors, excel formula between two dates, how to calculate number of days between two dates in excel, how to calculate purchase frequency, excel formula to calculate number of days between today and another date, excel date formula, excel formulas, improve excel performance with large files, check excel formula aspnet, excel formula calculate staying nights, excel formula days supply, excel formula calculate number pallets container, formula calculate projected sales excel, calculate average days sales uncollected, calculate average projected excel, excel formula calculate projected hours, excel formula project days

İşveren Hakkında:
( 4 değerlendirme ) Zalka, Lebanon

Proje NO: #17835879



Hello sir, excel expert here. I have completed half of your project. Actually, the most important part of the project. I am attaching example excel file. Usage: please write an ID in the box, for example "11" works fi Daha Fazla

%selectedBids___i_period_sub_7% gün içinde 25%project_currencyDetails_sign_sub_9% %project_currencyDetails_code_sub_10%
(14 Değerlendirme)

Bu iş için 12 freelancer ortalamada $40 teklif veriyor


Hello Sir, I am a full time freelancer and can work dedicated on this till completion. My name is Jay and I'm an Expert Excel and VBA programmer on freelancer.com and have been featured by the freelancer community ( Daha Fazla

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(640 Değerlendirme)

Hi Durman! Shouldn't it be like this: Work performed (No of times) divided by the days ? Please let me know via chat.

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(355 Değerlendirme)

Hello client I understand what logic needs to be built here. And what formula is needed here. I can help you. please contact me. Roy

in %bids___i_period_sub_35% gün içinde50%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(275 Değerlendirme)

Hello. I can do this......................................................................................

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(81 Değerlendirme)

Hi I am a very experienced statistician, data scientist and academic writer. I have completed several PhD level thesis projects involving advanced statistical analysis of data. I have worked with data from several comp Daha Fazla

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(61 Değerlendirme)

I can write the necessary formula to calculate average based on the first and last date the data was entered for I have excellent command on MS Excel formulas. I have completed complex projects in Excel before . For Daha Fazla

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(78 Değerlendirme)

Hello, After reading your project details I believe I'm suitable for this project. As I'm expert on this kind of project with more than 5 years experience. Please feel free to contact me. I am looking forward Daha Fazla

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(37 Değerlendirme)

I am an Excel specialist with a lot of experience in creating complex formulas and macros. You may check some of my projects here: [login to view URL]

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(54 Değerlendirme)

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

1 gün içinde %bids___i_sum_sub_32%%project_currencyDetails_sign_sub_33% USD
(21 Değerlendirme)

I think i can do to

in %bids___i_period_sub_35% gün içinde30%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(19 Değerlendirme)

Hi Good Day! I can help you to complete your excel file formula in less than a day. I am a vba macro developer for over 8 years now and has strong knowledge with Ms Excel Formulas and Functions. I created differ Daha Fazla

in %bids___i_period_sub_35% gün içinde20%project_currencyDetails_sign_sub_37% %project_currencyDetails_code_sub_38%
(14 Değerlendirme)