0

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.

3
  • Why would you want to exclude blank months? Let's say Person A had a bad month in March... no sales. Person B took vacation for the entire month of March... no sales. So you want to treat them both the same? Wouldn't you want to at least include March for Person A?
    – Isolated
    Dec 3, 2021 at 19:34
  • Yes—that is what I am saying. Exclude is the wrong word, perhaps. If someone had no sales in November, but I need the last 3 months of their data, I need this formula to capture Oct-Sep-Aug for them instead of Nov-Oct-Sep like it will for the other agents. Same principle for someone who has no sales in September—I want it to capture their Nov-Oct-Aug in their case. Etc. I hope that makes sense. I want everyone to have 3 data points.
    – Boswser
    Dec 3, 2021 at 20:31
  • I do not want to average in zeroes, I want them passed over by the formula and instead have it use the next most recent valid result.
    – Boswser
    Dec 3, 2021 at 20:33

2 Answers 2

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.

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

3
  • Hey thank you! I’m 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!
    – Boswser
    Dec 10, 2021 at 16:35
  • 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. I’m wondering—can 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.
    – Boswser
    Dec 10, 2021 at 16:36
  • 1
    Power query lets you sort the data this way during import, so this works great. Thank you!!
    – Boswser
    Dec 17, 2021 at 15:24
0

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.

Data list with 3 helper columns added

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.

Results list

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.
7
  • The use of MAXIFS can be avoided in cell F3. If the formula for cell F2 is changed to =IF(LEN(E2)=0,0,COUNTIF(E$2:E2,E2)) and copied downwards, the helper column labelled Row is no longer needed in the data list.
    – DMM
    Dec 5, 2021 at 12:45
  • Helper column Person2 can additionally be eliminated by changing F2 to =IF(C2=0,0,COUNTIFS(A$2:A2,A2,C$2:C2,">0")) and J2 to =MATCH(H2,IF($C$2:$C$21>0,$A$2:$A$21,""),0) and copying these formulas downwards in the data list and results list, respectively.
    – DMM
    Dec 5, 2021 at 13:15
  • Hey, this is really something! I’m sorry for the late response, my week has been crazy and I had to steer away from this project for a few days.
    – Boswser
    Dec 10, 2021 at 19:35
  • Unfortunately this is CLOSE but isn’t quite working—the formulas are, but the MCount column isn’t getting the months right because of the blanks in the E/Person2 column. For example, a salesperson’s most recent 3 nonzero sales months are 8, 10, 11. But their MCount only goes to 10 because it doesn’t read the blank for month 9, therefore their Sales3 column gets a 0 instead of their actual sales number for month 11. Does that make sense?
    – Boswser
    Dec 10, 2021 at 19:44
  • You need to adjust the formulae in columns I,J,N,O,P to take account of how much data you have in columns A-F. I used formulae including snippets such as $A$2:$A$21 (for example in cell I2) because I only used 20 rows of data in columns A-F. Had I used 10,000 rows of data I would have used I would have used $A$2:$A$10001 instead. You will have more success with Excel if you try and understand the formulae that have been used rather than simply copying and pasting answers.
    – DMM
    Dec 11, 2021 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.