How to Calculate Average of Last N Rows in Excel, Ignoring Blank Cells

Calculate Average of Last N Rows in Excel, Ignoring Blank Cells

Calculating the average of the last N rows in an Excel worksheet can be incredibly useful, especially when analyzing dynamic data sets. However, dealing with blank cells can prove to be a challenge in such a calculation. This article will walk you through the process of calculating the average of the last N rows in Excel, while ignoring blank (merged) cells.

Introduction

Some key techniques needed to compute this average include the use of the SUM, COUNT, and IF functions, as well as dealing with merged cells. The proposed method works for any rectangular range of N rows and columns, adjusting the formula based on the location of the target data in your Excel sheet.

Technique Overview

1. Identify the data range: First, define the range of data where your last N rows are located.
2. Find the last row: Next, determine the row number of the last row in your dataset using the COUNTA function.
3. Handle blank cells: Employ the IF function to exclude blank cells from your calculation.
4. Calculate the average: Finally, divide the sum of the non-blank, non-merged cells by the count of non-blank, non-merged cells to find the average.

Step-by-Step Instructions

Let's assume that you want to calculate the average of the last four rows, where the dataset has columns A through H (A8:H12), but you want to ignore blank cells.

1. Identify the data range: For our example, we have 4 rows and 8 columns, and the data is located in columns A through H of rows 8 to 12 (A8:H12).
2. Find the last row: To determine the last row in our dataset, we need to find the last non-empty row. Use the following formula:

   =COUNTA(A8:A12)
   

   In our example, this formula should return 4 since there are four non-blank cells among columns A8 to A12.

3. Handle blank cells: Let's exclude blank cells from the calculation. Use the following formula:

   =SUMIF(A8:H12, "*", A8:A12) / COUNTIF(A8:H12, "*")
   

   This formula handles text and numeric values. The trouble, however, is that the first part of the formula (SUMIF) would consider merged cells as a single value. To avoid this problem, we can split the data range into sub-ranges:

   =SUM(SUMIF(A8:A12, "*", A8:A12), SUMIF(B8:B12, "*", B8:B12), ... , SUMIF(H8:H12, "*", H8:H12)) / COUNTIF(A8:H12, "*")
   

   For our example, the formula would become:

   =SUM(SUMIF(A8:A12, "*", A8:A12), SUMIF(B8:B12, "*", B8:B12), ... , SUMIF(H8:H12, "*", H8:H12)) / COUNTIF(A8:H12, "*")
   

We've explored a technique for calculating the average of the last N rows in an Excel worksheet while ignoring blank cells. Since Excel doesn't support direct functionality for bypassing blank cells in a merged range, the formula needs to be adjusted and split to handle merged cells accordingly. With a bit of effort and the right formulas, you'll be able to get accurate results for any dataset.

References

• Type: Article
  Title: "COUNT, COUNTA, COUNTBLANK, and COUNTIF functions"
  Author: "Microsoft Support" Learn more »
• Type: Article
  Title: "SUMIF function"
  Author: "Microsoft Support" Learn more »