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.

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.

    =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.

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: