A common business requirement is getting an average of a data set of the past few days to include in a report. Perhaps you want to create a weekly average based on daily data. So how can we get the average from the previous week when the data is shown daily.
YouTube Average from previous week in Excel
Previous weeks average in MS Excel
As shown below we want to see the average for the week ending 9 April (so 3rd to 9th April) and the average for the week ending the 16th April (10th to 16th April). We want to create it in such a way that we can copy it down, so just by looking at the date in column D, it must know what to average.
The correct formula to use would be the AVERAGEIFS formula (note the plural- don’t get confused with AVERAGEIF).
So below we have told cell E5 to:
- Use column B as the range to average (Average Range)
- Look at column A for the Criteria1 range
- Find all items that are bigger than the date in cell D5 less 7 days. Note that we need to put inverted commas around the > and then use the CONCATENATE shortcut (&) to point to the cell D5
- Look at Column A again
- Find all the items that are less than or equal to the date in cell D5 (again using the CONCATENATE trick)
The end result will be that it will find the average for the 7 days previous to the date in cell D5. This can be copied down now.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.