# Choosing best function

Copper Contributor

# 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 tracks sales by order number and order date.  The manager wants to know how many days exist between sales orders per item sold in one calendar year.  Some items are more popular than others and, therefore, have more sales orders.

Which function / formula would calculate the days between sales order for each item, when the total number of sales orders differ per item?
4 Replies

# Re: Choosing best function

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.

# Re: Choosing best function

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.

# Re: Choosing best function

Here are two common approaches to calculate the days between sales orders for each item in a sporting goods store, considering different numbers of orders per item and within a specific year:

1. Using a conditional statement within a grouping:

This approach leverages the DATEDIFF function in Excel or equivalent functions in other spreadsheet software. It groups data by item and year, then calculates the difference between subsequent order dates within each group, using a conditional statement to handle the first order of each year. Here's an example formula in Excel:

Excel
=IF(A2=1, 0, DATEDIFF(A2, A\$1))
U

Where:

A2 is the order date for the current row.
A\$1 is the order date for the previous row within the same group.
This formula would be placed in a new column next to your order date data.
2. Using a helper column and grouping:

This approach introduces a helper column to identify the order number within each year for each item. Then, it groups data by item, year, and order number, and calculates the difference between subsequent order dates. Here's an example:

Helper column formula:

Excel
=IF(YEAR(A2)<>YEAR(A\$1), 1, A\$1 + 1)

Main formula:

Excel
=DATEDIFF(A2, A\$1)
Where

A2 and A\$1 are the order dates for the current and previous rows, respectively.
The helper column formula is placed next to the order date data.
The main formula is placed in a separate column and calculates the difference between subsequent order dates within each group identified by item, year, and order number.
Choosing the best function:

Both methods achieve the desired result. The first approach is simpler but might be less efficient for large datasets. The second approach requires an additional helper column but might be more performant for complex scenarios. Ultimately, the best choice depends on your specific data volume and your software's capabilities.