*When you want to calculate the average of the last four, five or any n ^{th }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

**Functions applicable:**

**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

*and*

**rows***to offset.*

**columns****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.

**First formula:**

COUNT(C3:C11)

**Syntax:** COUNT(value1, [value2], …)

**Step 1:**

**Result:** 9

It will give count of (C3:C11) i.e. 9.

**Second formula:**

OFFSET(C3,COUNT(C3:C11)-5,0,5)

**Syntax:** OFFSET(reference, rows, cols, [height], [width])

**Step 2:**

**Result:** C7:C11

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.

**Third formula:**

AVERAGE(OFFSET(C3,COUNT(C3:C11)-5,0,5))

**Syntax:** AVERAGE(number1, [number2], …)

**Step 3:**

**Result:** 30.4

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.