Forum Discussion
Choosing best function
I'm relatively new to using functions and formulas. I have a question regarding which type of function/ formula I should use for the following scenario. Scenario: A sporting goods store track...
I'm afraid it is more of a model question rather than a simple formula. The problem is not a one formula thing. I assume you want to calculate average gap (in days) between consecutive orders of the same item. In this case you will have to get a unique list of items. Then for each item you need to list consecutive sale dates and calculate the difference between each. After that apply average on top of your calculated differences.
Some functions you could use: UNIQUE(), AVERAGE(), SUM(), SUMIF(), SUMIFS(). I would also advise you to learn how to use pivot tables as they can become really handy for this and similiar scenarios.
Thank you for responding! Your assumption is correct. The data set was created and sorted using the UNIQUE-SORT function. I am familiar with pivot tables. However, not sure how to use one for large datasets that span an entire calendar year. The dataset I am working with has over 60,000 orders.