Data Choices

We compile. You decide.

The Average Function

Excel's Average Function is pretty straightforward. But many times people are working with a worksheet in which they are always adding new data, and they want averages to be taken of the latest data, not the earliest. Depending upon what you are looking for, this can be as simple as copying the AVERAGE formula down while utilizing relative cell references, or as complicated as writing an Excel macro in order to find the end of the data and then calculate the last few cells.

Let's start with the relative cell reference scenario.

In the above illustration Excel averages cells A4:A6, and since we have used a relative cell reference we can simply copy the formula down to get this:

Which of course makes cell B26 report the average of A24:A26 instead of the same average listed above. Now this works pretty well if you don't mind scrolling down the page each time to find the latest average. And lets face it, you have to go down there anyway to add the latest data to the column, so this solution will work just fine for most users.

But what if we wanted to be able to see the latest average at the top of the worksheet? It doesn't do any good to simply have cell C1, for example, refer to cell B26. Because once a new value is added in A27 the latest average will change but would not be reflected back into C1.

There are numerous ways that Excel can handle this problem, some easier than others. We wish to take a look at formula interaction, to show how the power of Excel can be easily expanded by using multiple formulas to solve the problem.

Here we have some new numbers and in cell C1 the current average of the last three values:

Now watch what happens when we input "21" into cell A26:

The new average, 33, appears right at the top where we want it!

As you can probably tell from the column headers, column A is the data entry section, column B queries column A to see if there is a value present or if the cell is blank, column C ensures that it is the first blank cell and column D reports the average of the last three data items if and only if they actually ARE the last three data items! So let's take a look at how we did that.

In cell B3 simply put in

=ISBLANK(A3)

and copy that formula down through the reasonable length of the worksheet, perhaps 500 to 1000 cells. Just don't forget where the formulas end or you will stop getting valid data! The ISBLANK formula just asks Excel to test and see if data exists, and if it does it returns TRUE, or FALSE if not.

So column C asks if it is the FIRST BLANK CELL or not. Of course we want the data preceding the first blank cell, so in C3 we enter

=AND(B3=TRUE,B2=FALSE)

Here, if the cell to its left is TRUE (column A cell blank) AND the cell immediately above this formula reported FALSE, indicating data, then this formula returns TRUE, meaning that it is the first blank cell.

Then in cell D5 we put in

=IF(B5=TRUE,AVERAGE(A1:A4),"")

and copy that down. This formula simply says that if the conditions we want exist average the last three cells, otherwise make the cell visually blank. (You could also use the number zero if you prefer)

And finally we simply put

=SUM(D:D)

in the cell we want the data reported. We could also use the more familiar =SUM(D3:D500) if we were limiting the worksheet to the first 500 rows.

Examples like this are an excellent learning tool! While there are other methods that will do the same thing, and some may fit your needs better than this example, this example show you how to:

Break down a problem into parts

Utilize multiple formulas interactively to reach your end goal

Report the results in a manner that allows you to pass the data on to others who will not have to search through the worksheet to find the information they need!

Domain Pro Supersaver SEO 12 Month Package

Home Services Search Engine Marketing Specialists Internet Marketing Specialists DCI Calculator