# Averaging a subset of data within a range

1. ## Averaging a subset of data within a range

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
Chris Lewis
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.11583333
20.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

2. ## Re: Averaging a subset of data within a range

Not very clear...

If you're looking to take lowest 10 of both columns in first 20 values appearing in both columns... and assuming blanks are blanks and no row has 2 numbers then perhaps:

``Please Login or Register  to view this content.``
confirmed with CTRL + SHIFT + ENTER

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1