How to use one column of text as an identifier to average a second column number

the average() function will do the trick:

The data table in the example is titled "Table 1"

for the summary table (the lower table), enter the names in column A.

B2=AVERAGEIF(Table 1::B, A2, Table 1::C)

this is shorthand for .... select cell B2, then type (or copy and paste from here) the formula:

=AVERAGEIF(Table 1::B, A2, Table 1::C)

select cell B2, copy

select cells B2 thru the end of the column, paste

if you want the averages to be empty when there is no name type in column A use this formula instead:

B2=IF(LEN(A2)=0, "", AVERAGEIF(Table 1::B, A2, Table 1::C))

Ennoxx,

Here's the deal:

The expression for Average Calorie Burn is:

=AVERAGEIF(Log::B, A2, Log::C)

Regards,

Jerry

Nice solution Jerry 😉

Sorting in Numbers V3 is a mess. An easy way to avoid the problem is to use LOOKUP. Here's an example for your case:

The expression in RANK of Summary aux table is:

=RANK(B, B)

The expression in Trainer column of Summary by Trainer is:

=LOOKUP(ROW()−1, Summary aux table::C, Summary aux table::A)

The expression in Average Calorie Burn in Summary by Trainer is:

=LOOKUP(ROW()−1, Summary aux table::C, Summary aux table::B)

By doing this, the row of the table determines which rank is displayed. The Aux table may be hidden by putting it on another Sheet or by putting a White Shape over it.

Jerry

There is an enhancement that prevents problems with tie scores which you may want to consider. Just a small amount of additional code:

By adding a very small offset depending on the row in the table, there will never by a tie.

The expression in Summary aux table, Column B is now:

=AVERAGEIF(Log::B, A2, Log::C)+ROW()÷10000

and, the expression now in Summary by Trainer, Column B is now:

=ROUND(LOOKUP(ROW()−1, Summary aux table::C, Summary aux table::B), 0)

Regards,

Jerry