Hi everyone! I appreciate any help you can offer. I’ll try to be concise:
I have a 3-column table. Month, 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. It would need to skip over the blank and grab the next most recent month to total 3 data points.
Right now I have a very simple pivot table for this. I was filtering for the last 3 months and getting the average value there. This was working great for my purposes until I realized that salespeople didn’t always have data for each month, so some people’s averages were only from 1 or 2 results.
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.