Excel: How can I get the most recent 3-month average, but not use blank months?

Question

Hi people better at Excel than me! I appreciate any help you can offer. I’ll try to be brief:

I have a 3-column table. Month (as 1,2,3,etc…), Salesperson Name, and Sales. Each row is how many $ in sales a salesperson got for that month.

It’s arranged like: first 1000 rows are all Jan/1, next 1000 all Feb/2, etc.

I need to get the average $ amount sold by each salesperson for last 3 months. But—-if one of those last 3 months is blank, I still need a 3-month average.

So I need the formula to recognize, for example, a blank September result, and go get the next most recent result instead (Aug, July, whatever it may be), so it’s always averaging the latest 3 months of that salesperson’s results, and not just 1 or 2 results.

Right now I have a very simple pivot table for this. Was working great for my purposes until I realized this issue :(

Is there any way (pivot table or not) that this can be done? There’s over 1000 salespeople, so manually is not feasible. I have lots of people without numbers for a month for whatever reason, so I need to make the calculation work around that.

Thanks for any help! Please let me know if I can include anything else or if it’s not clear.

Answer 1

I don't know of a great way to average the three most recent non-blank months, but here's a very hacky way to do it. This may spark some better ways to accomplish it.

In your raw data, create a table, and then sort by month (descending) and person.

Next, filter on the Sales column and de-select blanks. Then copy those contents to another area and paste.

Then create a "Rank" column. Formula for cell D2, which ranks each month (most recent is 1, etc) for each person.

    =IF(B2=B1,D1+1,1)

Then have another area with your distinct people (column F), and placeholder columns for their most recent 3 months (columns labled 1, 2 and 3).

Cell G2 formula:

    =SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)

Drag it over to column title 3 and down. Lastly, create your average formula.

Answer 2

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

=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")

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

=MATCH(H2,$E$2:$E$21,0)

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

=I2-1

=K2+1

=L2+1

The formula in cell N2 is

=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)

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

=IF(C2>0,1,0)

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

=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))

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.

Notes

1. The use of MAXIFS requires Excel 2019 or later
2. 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.
3. In the images, a blue font indicates a formula and a black font a static value.