Have you ever needed to get an average of a group of numbers in an Excel spreadsheet? And have you ever needed to do so with a range that is variable? In other words, “I need to get an average of Column B, but I don’t know upfront how many rows are going to be in Column B.”
Well, it turns out to be fairly simple in Excel. I’m partly sharing this with the world to be a help to others, but also because I forget things… and I needed a place to keep it!
First, the basics. If you are reading this, then you should know that to get an average of a range of cells in Excel, you set the content of the cell to a function that looks like this:
=AVERAGE(B2:B20)
This basically tells Excel to give me an average (remember your grammar school arithmetic?) of the numbers in cells from the B2 position to the B20 position. The tricky part of the variable range conundrum is that we don’t know what to put in the second part of the range. Will it be B20 or B23? Who knows!?
To solve this problem, we can use the COUNTA function which “counts the number of cells in a range that are not empty.” If you couple this with the fact that specifying a range as B:B tells Excel to reference the entire column, then we can just have COUNTA give us the row number of the last non-blank column. You can try it out by making your cell function as follows:
=COUNTA(B:B)
If the last row was actually row 23 in our running example, the value for this column (the one with the formula) should now be “23”. Now we just need to get this information into our “range” calculation for the average. To do that, we’ll use the INDIRECT function which “returns the reference specified by a text string.” So now we change our working example to the following function:
=INDIRECT("B" & COUNTA(B:B))
You can see that we build a string by giving the column letter “B” and concatenating it (using the ampersand) with the number of the last row with data. This string is passed to the INDIRECT function, which just gives us a reference to that very last cell.
Now, let’s put it all together!
=AVERAGE(INDIRECT("B2"):INDIRECT("B" & COUNTA(B:B)))
The cell which contains this formula should give us the average of the numbers between B2 and whatever the last non-blank cell is in the B column.