Formula Help - Average Last 5 Values

[JonnyMa]

I am using Excel 2007 on Windows 7.

I have a sheet that I use to track employees and their sales numbers.

On sheet 1, I have:
column A - year (e.g. 2013) (column named YEAR)
column B - week (e.g. Jun 3 - Jun 9) (column named WEEK)
column C - region (North America, South America, Europe, Asia, Australia) (column named REGION)
column D - employee name (lastname, firstname) (column named EMPLOYEE)
column E - sales in $ for that week. (column named SALES)

All employees are listed on the same sheet. The sheet is sorted by the year then week, so new values are added to the bottom.

On sheet 2, I have:
column A - year
column B - region
column C - employee name
column D - total sales on year
column E - average sales/week on year
column F - average sales/week on last 5 weeks.

The problem I'm having is with the formula for column E in calculating the average on the last 5 weeks per employee.

For column E, I used, =AVERAGEIFS(SALES,YEAR,$A3,REGION,$B3,EMPLOYEE,$C3)

I'm not sure if I am able to adapt this formula to column F or if a different formula is needed. Most employees have more than 5 weeks of sales so far this year, but we do also have a few new employees with less than 5 weeks of sales. The formula for column F should also be able to adjust for employees with less than 5 weeks.

Any help with this formula would be appreciated. Thank you!

[oeldere]

If you are able to make the weeknummer in column F, you can define the last 5 weeks by formula in column G.

After that you can use pivot table, to get the result.

[JonnyMa]

I'm not looking to use pivot tables. Is there a formula that can grab the last 5 values? I also need it to continuously take only the last 5 values when I update sheet 1. I don't want to have to go update the formula each week.

[oeldere]

I read in you question that you don't want pivot table, but it's a very powerfull tool for this kind of questions.

If you have worked with them, I bet you agree.

Have said that, the helpcolumns can also be used in a formula-solution, so I think it's the way to go.

[JonnyMa]

After googling for formulas, I can across this solution, but it doesn't work properly. I'm not sure what I did wrong.

=(SUMIFS(SALES,YEAR,$A$1,REGION,$A3,EMPLOYEE,$B3)-IF(COUNTIFS(YEAR,$A$1,REGION,$A3,EMPLOYEE,$B3)>4,SUMIFS(SALES:(SALES,COUNT(SALES)-4),YEAR,$A$1,REGION,$A3,EMPLOYEE,$B3),0))/5

[oeldere]

How about sharing the excel file, without confidentional information?

Please also add the desired (expected) result.

[Melvinrobb]

It would be best to upload a sample workbook with some mock data, showing what values you would want the formula in column F to calculate given the different scenarios.

[JonnyMa]

I just finished creating a mock tracker of what I am using. I have attached it here.

[oeldere]

I made the pivot table on sheet 1 (just an exampe, there are more options)

The formula you find in the green cells on sheet 2.

[JonnyMa]

Thanks for your help.

Unfortunately, it doesn't seem to work if I need it to do a running average unless I update the values manually.

=SUMPRODUCT((Sheet1!$G$4:$G$65="take this value in an avarage")*(Sheet1!$D$4:$D$65=C2)*(Sheet1!$E$4:$E$65))/SUMPRODUCT((Sheet1!$D$4:$D$65=Sheet2!C2)*(Sheet1!$G$4:$G$65="take this value in an avarage"))

Also when I replace the references with the labels that I created for the columns, it seems to cause your formula to generate an error. For example, column D is EMPLOYEE and column E is SALES.

[oeldere]

Instead of your tekst please post your sheet, then I can look at the file, what's going wrong.

All ranges has to be the same in a sumproductformula.

Sheet1!$G$4:$G$65 and Sheet1!$D$4:$D$65 is correct

Sheet1!$G$4:$G$65 and Sheet1!$D$4:$D$723 gives an error

[JonnyMa]

This gives an error too:

Sheet1!$G:$G and Sheet1!$D:$D

[JonnyMa]

I solved it by using a modified version of oeldere's formulas.

Thank you!