Calculating frequecy (Min/Max/Average) of order placement in Excel

Question

I need to analyze Weekly order frequencies over last 1 year period to find out what is the min/max/average frequencies of orders for each product. whether it is new or old,system should calculate the first occurrence of the order in the year as the starting week of the order. Min order frequency is difference between successive ordering weeks. If the first order is in wk 3 and the second order is in wk6, implies the order frequency is 3 weeks (=>6-3). Orders can be at any week in the past 52 weeks. Average order frequency = (52 - First order week) / no of weeks that have orders.

Attaching the excel for better understanding the issue.

Original image

+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+
| Product | wk1 | wk2 | wk3 | wk4 | wk5 | wk6 | wk7 | wk8 | wk9 | wk10 | wk11 | wk12 | wk13 | wk14 | wk15 | wk16 | wk17 | wk18 | wk19 | wk20 | wk21 | wk22 | wk23 | wk24 | wk25 | wk26 | wk27 | wk28 | wk29 | wk30 | wk31 | wk32 | wk33 | wk34 | wk35 | wk36 | wk37 | wk38 | wk39 | wk40 | wk41 | wk42 | wk43 | wk44 | wk45 | wk46 | wk47 | wk48 | wk49 | wk50 | wk51 | wk52 | Order start wk | Order frequency (Weeks) |     |                                   |  |
+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+
|         |     |     |     |     |     |     |     |     |     |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |                | Min                     | Max | Average                           |  |
|         |     |     |     |     |     |     |     |     |     |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      |                |                         |     | (End wk - Start week)/No of times |  |
| SKU 1   |     |     |     |     |     |     |     |     | y   |      | y    |      | y    |      | y    |      | y    |      | y    |      | y    |      | y    | y    |      |      | y    |      | y    |      | y    |      | y    |      |      |      |      |      | y    |      | y    |      | y    |      | y    |      | y    |      | y    |      | y    |      |              9 | 1                       | 6   | 2.15                              |  |
| SKU 2   |     |     |     |     |     |     | y   |     |     |      |      |      | y    |      |      |      |      |      | y    |      |      |      |      |      | y    |      |      |      |      |      | y    |      |      |      |      |      | y    |      |      |      |      |      | y    |      |      |      |      |      | y    |      |      |      |              1 | 0                       | 0   | 7.29                              |  |
| SKU 3   |     |     |     |     |     |     |     |     |     |      |      |      |      |      | y    |      |      |      |      |      |      |      |      |      |      |      |      |      |      |      | y    |      |      |      |      |      |      |      | y    |      |      |      |      |      |      |      | y    |      |      |      |      |      |             15 | 8                       | 15  | 9.25                              |  |
+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+

Welcome to [so]! Do you think this image helps "better understand the issue"? Please check out "[ask]" as well as "[mcve]", and then you can [edit] your question to make it more clear. (Down-voters tend to retract their votes if the question is improved.) — ashleedawg, Jun 02 '18 at 05:06
Don’t understand how you get a result of 6 when there are a lot more y ... But, as a hint, you should check out sumi() sumifs() countif() and countifs() ... — Solar Mike, Jun 02 '18 at 06:09
@SolarMike [sumi](https://en.wikipedia.org/wiki/Sumi_Jo)? :) — ashleedawg, Jun 02 '18 at 07:02
TBH I wouldn't have a clue how to solve this without helper rows. I would have had to ask...Barry Houdini https://stackoverflow.com/questions/30633434/how-to-find-largest-sequence-of-a-given-number-in-excel?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa . It's actually quite an interesting question but let down by presentation — Tom Sharpe, Jun 02 '18 at 08:16

Tom Sharpe · Answer 1 · 2018-06-03T09:22:37.350

So as mentioned @Barry Houdini solves the problem of finding the longest sequence of zeroes separated by ones elegantly here

You only have to change it slightly to check for repeated blank cells separated by 'y'. The only thing is that you don't want to include cells before the first 'y', and (although this isn't clear) may not want to include blank cells after the last 'y'.

The formula for MIN becomes

=MIN(IF((ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))>1)*(ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))<COUNTA(B4:BA4)+1),FREQUENCY(IF(B4:BA4="",COLUMN(B4:BA4)),IF(B4:BA4="y",COLUMN(B4:BA4)))))+1

and the formula for MAX becomes (the same)

=MAX(IF((ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))>1)*(ROW(A$1:INDEX(A:A,COUNTA(B4:BA4)+1))<COUNTA(B4:BA4)+1),FREQUENCY(IF(B4:BA4="",COLUMN(B4:BA4)),IF(B4:BA4="y",COLUMN(B4:BA4)))))+1

where you need to add 1 to make the results agree with the question because @Barry's formula counts numbers of blanks but OP wants interval between two successive y's. An array of ny+1 elements is generated where ny is the number of y's. This is because the FREQUENCY function returns an array with n+1 elements where n is the number of cut points (bins_array in documentation and because the column numbers of cells containing y are used as cut points so there are ny of them.

These are both array formulas and need to be entered with CtrlShiftEnter

The formula for the average is just

=(COLUMNS(B4:BA4)-MATCH("y",B4:BA4,0))/COUNTA(B4:BA4)

Calculating frequecy (Min/Max/Average) of order placement in Excel

1 Answers1

Linked