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:

1 |
=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:

1 |
=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:

1 |
=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!

1 |
=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.