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.


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.

enter image description here

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.


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:


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

enter image description here

Comments (3)

  • +0 – Hey thank you! Im so sorry for my delayed response, I have had a crazy week and had to back-burner this project for a little while. This is a great workaround fix that I can absolutely use! — Dec 10, 2021 at 16:35  
  • +0 – My goal now is to try to find a way to make this user-friendly. Right now the table is created by a Power Query that pulls in the data from another tab. Im wonderingcan I alter the Power Query so that it sorts the data like this automatically? Then I can just stick these formulas next to it so that the recipients can view this without me needing to sort and calculate it each time. — Dec 10, 2021 at 16:36  
  • +1 – Power query lets you sort the data this way during import, so this works great. Thank you!! — Dec 17, 2021 at 15:24  

External Links

External links referenced by this document: