Puzzler: Avg Low 10 of 20

Puzzler: How to average the the lowest 10 numbers from a list of 20.

Assume that you have a vertical range of Stock Closing Prices from Yahoo Historical in Column G. They will be in reverse chronological order. We want to average the lowest 5 values from the previous 20 days. I will not defend "why", just suppose we want to.

If Sept 8 is G2, the the 20 days would be in G2:G21. A single cell formula to average the lowest 5 of 20 would be:

=AVERAGE(SMALL($G2:$G21,{1,2,3,4,5}))

Note, you do NOT need to enter this as an Array function. (This surprised me in the SIG meeting.) Apparently the {} are enough to tell Excel that SMALL returns an array and AVERAGE works on it just as it would a range.

If you want to average the top 5 of the past twenty:

=AVERAGE(LARGE($G2:$G21,{1,2,3,4,5}))

-Stephen Rasey 20040909.

The Idea for this Puzzler came from:
From: microsoft.public.excel.programing: 8/23/2004
Picking the latest entries from a column:

I have a problem I'm trying to solve for my spreadsheet. I have a column of
data which I'm trying to use the latest 20 entries and get the average of
the ten smallest values of those 20. How do I accomplish this? In my
current setup, the user will always enter the new data on the last row and
so the lastest 20 values will be the 20 rows from the bottom.

In the mean time, I've rearranged the data entry in descending order so that
the latest one is on top and I can just look at the top 20 cells.

Thanks!

Click for Solution