Explanation
The formulas shown in the example all use the AVERAGE function with a relative reference set up for each specific interval. The 3-day moving average in E7 is calculated by feeding AVERAGE a range that includes the current day and the two previous days like this:
=AVERAGE(C5:C7) // 3-day average
The 5-day and 7-day averages are calculated in the same way. In each case, the range provided to AVERAGE is enlarged to include the required number of days:
=AVERAGE(C5:C9) // 5-day average
=AVERAGE(C5:C11) // 7-day average
All formulas use a relative reference for the range supplied to the AVERAGE function. As the formulas are copied down the column, the range changes at each row to include the values needed for each average.
When the values are plotted in a line chart, the smoothing effect is clear:
Insufficient data
If you start the formulas in the first row of the table, the first few formulas won’t have enough data to calculate a complete average, because the range will extend above the first row of data:
This may or may not be an issue, depending on the structure of the worksheet, and whether it’s important that all averages are based on the same number of values. The AVERAGE function will automatically ignore text values and empty cells, so it will continue to calculate an average with fewer values. This is why it “works” in E5 and E6.
One way to clearly indicate insufficient data is to check the current row number and abort with #NA when there are less than n values. For example, for the 3-day average, you could use:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
The first part of the formula simply generates a “normalized” row number, starting with 1:
ROW()-ROW($C$5)+1 // relative row number
In row 5, the result is 1, in row 6 the result is 2, and so on.
When the current row number is less than 3, the formula returns #N/A. Otherwise, the formula returns a moving average as before. This mimics the behavior of the Analysis Toolpak version of Moving Average, which outputs #N/A until the first complete period is reached.
However, as the number of periods increases, you will eventually run out of rows above the data and won’t be able to enter the required range inside AVERAGE. For example, you can’t set up a moving 7-day average with the worksheet as shown, since you can’t enter a range that extends 6 rows above C5.
Variable periods with OFFSET
A more flexible way to calculate a moving average is with the OFFSET function. OFFSET can create a dynamic range, which means we can set up a formula where the number of periods is variable. The general form is:
=AVERAGE(OFFSET(A1,0,0,-n,1))
where n is the number of periods to include in each average. As above, OFFSET returns a range that is passed into the AVERAGE function. Below you can see this formula in action, where n is the named range E2. Starting at cell C5, OFFSET constructs a range that extends back to previous rows. This is accomplished by using a height equal to negative n . When E5 is changed to another number, the moving average recalculates on all rows:
The formula in E5, copied down, is:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Like the original formula above, the version with OFFSET will also have the problem of insufficient data in the first few rows, depending on how many periods are given in E5.
In the example shown, the averages calculate successfully because the AVERAGE function automatically ignores text values and blank cells , and there are no other numeric values above C5. So, while the range passed into AVERAGE in E5 is C1:C5, there is only one value to average, 100. However, as periods increase, OFFSET will continue to create a range that extends above the start of the data , eventually running into the top of the worksheet and returning a #REF error.
One solution is to “cap” the size of the range to the number of data points available. This can be done by using the MIN function to restrict the number used for height as seen below:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
This looks pretty scary but is actually quite simple. We are limiting the height passed into OFFSET with the MIN function :
MIN(ROW()-ROW($C$5)+1,n)
Inside MIN, the first value is a relative row number , calculated with:
ROW()-ROW($C$5)+1 // relative row number..1,2,3, etc.
The second value given to MIN is the number of periods, n. When the relative row number is less than n, MIN returns the current row number to OFFSET for height. When the row number is greater than n, MIN returns n. In other words, MIN simply returns the smaller of the two values.
A nice feature of the OFFSET option is that n can be easily changed. If we change n to 7 and plot the results, we get a chart like this:
Note: A quirk with the OFFSET formulas above is that they won’t work in Google Sheets, because the OFFSET function in Sheets won’t allow a negative value for height or width. The attached spreadsheet has workaround formulas for Google Sheets.
Explanation
In this example, the goal is to create a formula that will return “Pass” or “Fail” depending on whether a student has a passing score in at least 4 out of 6 subjects. This problem can be easily solved with a formula based on the COUNTIF function together with the IF function in a formula like this:
=IF(COUNTIF(C5:H5,">=70")>=4,"Pass","Fail")
COUNTIF function
The COUNTIF function counts cells in a range that meet a single condition, referred to as criteria . COUNTIF supports logical operators (>,<,<>,<=,>=) and wildcards (*,?) for partial matching. The generic syntax for COUNTIF looks like this:
=COUNTIF(range,criteria)
In this case, we need to configure COUNTIF to count passing scores in columns C:H for each name listed in column B. We start off with the range :
=COUNTIF(C5:H5,
For criteria , we need to use the greater than or equal to operator (>=) with the passing score of 70:
=COUNTIF(C5:H5,">=70")
Notice the criteria is entered in double quotes (""), which is a quirk of RACON functions in Excel . The formula above is in fact the formula entered in cell J5, copied down. As you can see in the worksheet shown above, COUNTIF returns a count of passing scores in each row. If desired, column J could be used as a helper column , but in this example the formula in column K is an all-in-one formula and the formula in column J is just for reference.
IF function
To return “Pass” or “Fail”, we use the IF function . We start off by nesting COUNTIF inside IF like this:
=IF(COUNTIF(C5:H5,">=70")
We know from above that the COUNTIF formula returns a count of passing scores. Because the goal in the example is to return “Pass” when there are at least 4 subjects with passing scores, we add logic to test the result from COUNTIF:
=IF(COUNTIF(C5:H5,">=70")>=4,
This is the logical_test inside the IF function. If COUNTIF returns the number 4 or greater, the logical test will return TRUE. Otherwise, the logical test will return FALSE. Finally, we need to add the value_if_true and value_if_false arguments and close up the formula. The final formula in K5 is:
=IF(COUNTIF(C5:H5,">=70")>=4,"Pass","Fail")
As the formula is copied down, if 4 or more subjects have a passing score of at least 70, IF returns “Pass”. Otherwise, the IF function returns “Fail”.
Must pass Math and English
If Math and English must have passing scores , regardless of other scores, the formula can be adjusted like this:
=IF(AND(COUNTIF(C5:H5,">=70")>=4,C5>=70,F5>=70),"Pass","Fail")
Here the AND function is used for the logical_test inside IF:
AND(COUNTIF(C5:H5,">=70")>=4,C5>=70,F5>=70)
With this configuration, AND will return TRUE only when these three conditions are TRUE:
- Passing score in 4 out of 6 subjects
- Passing score in Math
- Passing score in English
If any condition is not true, AND will return FALSE and the IF function will return “Fail” as a final result
