Calculating the mean from a frequency table: Array formula example

Array formulas are difficult to get your head around, but once you start understanding the way in which they work, you will find that there is a lot to love about array formulas (sometimes called CSEs because you have to type the formula and then press Ctrl+Shift+Enter to have it entered as an array formula). Of course, the converse sometimes also happens—once someone “gets” array formulas, they start using them for all kinds of tasks, even where non-array equivalents exist. That is generally not a good thing, as array formulas are computationally cumbersome, and also have other drawbacks, chief of which is that they can be edited and entered with only the Enter button instead of Ctrl+Shift+Enter. Sometimes, this results in an error value, which immediately shows the problem, but other times, the non-array formula returns a result, although that result is different (and therefore incorrect) from the same formula array-entered.

Here, however, is one nice little application of an array formula which shows their power.

It is not uncommon for me, as a statistician, to receive a table like this:

Figure 1    Frequency table

Granted, I tend to work with raw data, not processed data as in the table, but the simple fact is that tables like this do come across my path from time to time.

How, then, can I calculate something like the mean age of the respondents listed in this table? I cannot simply calculate the mean of the age column, nor the mean of the counts column, as the following figure shows (I am using one of Excel 2013’s great new functions, FORMULATEXT, to display the formulas in A16:C16):

Figure 2    AVERAGE function gives totally wrong answer when applied to wrong values

But with a very simple array formula, I can “expand” the data in the table, and then calculate the mean of that (here, FORMULATEXT shows the formulas in C1:C2):

Figure 3    Simple array formula “expands” the frequency table and allows me to calculate the average from the expanded results

I should add, as a side note, that {=AVERAGE(A4:A14*B4:B14)} does not give me the desired results, in case you were wondering why my contrived formula for calculating the average was necessary.

Simple, but very effective. And that is why I love array formulas!

Share