General Computing
microsoft-excel pivot-table average
Updated Fri, 15 Jul 2022 11:44:25 GMT

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

Hi people better at Excel than me! I appreciate any help you can offer. Ill 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.

Its 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 its always averaging the latest 3 months of that salespersons 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? Theres 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 its not clear.

## Solution

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.