I have a known range with 1000 rows and 2 cols. The range only contains 50 values in each column and they can be anywhere within 1000 rows. I want to know how to average the 10 smallest values within the upper 20 values. Please see the example below.
Dave Smith 30.54275362
33.81811594
5.171186441
20.49322034
19.53559322
19.11583333 20.0575
20.99916667
21.94083333
22.8825
23.82416667
29.11186441
28.15423729
27.19661017
26.23898305
30.54275362
33.81811594
5.171186441
20.49322034
19.53559322
19.1158333320.0575
20.99916667
21.94083333
22.8825
23.82416667
29.11186441
28.15423729
27.19661017
26.23898305
I know that you can do:
=average(small(named_range,{1,2,3,4,5}))
to average the 5 smallest values within named_range. I am not sure how to dynamically adjust named_range so that it only includes the first 20 cells with values.
I know how to do this with VBA but would prefer to do it with excel worksheet functions.
Any help would be appreciated.
Thanks,
Will
Bookmarks