Calculating frequecy (Min/Max/Average) of order pl

问题:

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                              |  |
+---------+-----+-----+-----+-----+-----+-----+-----+-----+-----+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+------+----------------+-------------------------+-----+-----------------------------------+--+

回答1:

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)