Taking a different approach which leaves the original data intact, without reordering, filtering or copying, 3 helper columns are added to the data list as shown next.
Column Row is a simple index series starting at 1, column Person2 is the same as Person but uses an IF function to insert a null string in rows with zero Sales. MCount provides a count, by Person, of months with non-zero Sales.
MCount interacts with a results list which contains a row for each sales person, as shown next.
In the results list, column Months simply counts the number of months with non-zero sales in the data list for each sales person. The formula in cell I2 is
RowM1 shows the Row value in the data list corresponding to the first month with non-zero sales for each sales person. The formula in cell J2 is
Columns Month1, Month2 and Month3 are the month numbers within column MCount (of the data list) corresponding to the first, second and third months used for calculating the 3-month average, whilst Sales1, Sales2 and Sales3 are the sales values for these 3 months. Average is the calculated 3 month average.
The formulae for cells K2, L2 and M2 are respectively
The formula in cell N2 is
and relies on the values in the MCount column of the data list (range
$F$2:$F$21, see next). Cell N2 is copied to cells O2 and P2.
The range I2:Q2 can be copied to all subsequent rows of the results list.
Data list - column MCount
The formula in cell F2 is
This puts 0 or 1 into the cell. 0 indicates that the person in cell E2 had no sales in the month shown in cell
B2, whilst 1 indicates that it was the first month of sales for this person.
The formula in cell F3 is considerably more complex and is
The TRUE part of the first IF simply delivers 0 if the row corresponds to Sales of zero.
Otherwise, Sales are non-zero and the second IF is triggered. In this second IF the Person in cell E3 is looked up in the results table and, if the RowM1 value (row number in data list corresponding to first month of sales) matches the Row value in cell D3, this means that row 3 corresponds to the first row with non-zero sales for the person identified in cell E3.
If the RowM1 and Row values differ, row 3 in the data table corresponds to a subsequent month of sales (after the first) for the person in cell E3. In this case, the number of the month is obtained by adding 1 to the maximum value in column F for the sales person in cell E3 in the preceding rows of the data list. A MAXIFS function is used to determine this maximum.
The formula in cell F3 is copied to subsequent rows of the data list. The appropriate use of relative and absolute addressing in the formula ensures that the MAXIFS function uses the appropriate ranges of cells in the copies.
- The use of MAXIFS requires Excel 2019 or later
- It is assumed that (i) data is in chronological order and (ii) there is, at most, one non-zero row of sales data for each sales person and month combination.
- In the images, a blue font indicates a formula and a black font a static value.