Averaging scores

[birdieman]

I am trying to select the 10 lowest scores from the last 20(changes weekly)
and average them. What is the easiest way to perform this function.

 

[Shane Devenshire]

Hi,

The general idea would be to use an array formula like:

=AVERAGE(SMALL(A1:A20,ROW(1:10)))

to make this an array you press Shift+Ctrl+Enter to enter the formula.

To be more specific we need to know how is your data laid out. By last 20
do you mean the latest (newest) based on a dated, or do you mean the last
entries in a column.

If you mean bottom 20 then use the following array:

=AVERAGE(SMALL(OFFSET(A1,MATCH(TRUE,A1:A100<>"")-1,,-10),ROW(1:10)))

Where you data may extend down to row 100.

 

[Bob Bridges]

The obvious way is to sort the data first, but I'm presuming you thought of
that already and don't want to do it. Ok, try this: Say your 20 changes are
in $A$2:$A$21. In $A$22 put the median value:

=MEDIAN(A$2:A21)

The median value, in case you don't remember from high-school statistics, is
the middle value; if you have 10, 12 and 5, the median value is 10. If there
are an even number of values, Excel calculates the median as halfway between
the middle two. Now in column B put this formula:

=IF($A2<$A$22,$A2,"")

That populates column B with the lower half of the values in column A -
opposite the upper half, the cells are left blank - and in B$22 you can take
the average:

=AVERAGE(B$2:B22)

 

[T. Valko]

Try this array formula**.

It will work according to these conditions:

If there are 0 scores entered it will leave the cell blank.
If there are <10 scores it will average whatever scores are available.
If there are >=10 and <=20 scores it will average the lowest 10 scores.
If there are >20 scores it will average the lowest 10 scores out of the last
20 scores.

Assumes scores are on row 2.

=IF(COUNT(2:2),AVERAGE(SMALL(INDEX(2:2,LARGE(IF(ISNUMBER(2:2),COLUMN(2:2)),MIN(COUNT(2:2),20))):IV2,ROW(INDIRECT("1:"&MIN(COUNT(2:2),10))))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.