Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.
2. How do you take all the formatting from chart (see 1 below) and PASTE it onto another chart (2)? Guess and then see how it can be done with 'See Answer'



Get the average from the previous week (or other dates)

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.

Average from the previous week

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)
Average from the previous week

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.

SUMIFS Value Error

Financial model reviews on SUMIF/S, COUNTIF/S, AVERAGEIF/S

Variable moving average formula in Excel