When you want to calculate the average of the last four, five or any nth values in the columns or rows with the help of formula offset and count.
Query: We have the name of the student with marks of each test. Our task is to get the average marks of test 5 to test 9 (C7: C11) of Joe. Same find for all students.
Formula to implement:
Note: You can simply add the Excel filter to average only the Lowest and Largest Nth value in a range with this formula.
Download Your Example Excel File
Average: Get an average of numbers positioned in one or multiple ranges.
Offset: The Excel OFFSET function returns one or multiple data points of a data set by providing a reference point of data set with a specified number of rows and columns to offset.
Count: The Excel COUNT function compute the count of numbers within the reference list of arguments ignoring the text and blank cells.
Formula Explanation (step by step)
Excel starts calculating innermost parenthesis than work outward. You can also debug the formula using a shortcut F9 function key under the formulas tab.
Let’s break the formula into multiple small steps to understand its application.
Syntax: COUNT(value1, [value2], …)
It will give count of (C3:C11) i.e. 9.
Syntax: OFFSET(reference, rows, cols, [height], [width])
After giving count (9), OFFSET function decrease the range by 5 so new starting point is C7 from C3 with height are of 5 (C7:C11). The last 5 numbers.
Syntax: AVERAGE(number1, [number2], …)
77+12+0+40+23/5= 152/5= 30.4
It will give the average of last five numbers in the given range C7:C11(77+12+0+40+23).
Note: If you want to find the average of the highest values in the array.