Find the average of the lowest four values of the last six

**pjmcc64** · 05-03-2010, 01:11 PM

I have a spreadsheet that holds values in a row. I want to look at the string of numbers, and find the average of the lowest four of the last six. Here is an example:

45 43 47 42 47 47 45 38 45 47 44 41 39 39
51 48 55 55 48 50 52 46 50 51 55
46 43 45 44 47 39 39 42 42 43 48
43 42 42 42 39 45 38 38 40 42 39 43 41 40
52 57 54 55 57 53 57 49 57 51 59 61
46 42 45 42 38 41 39 38 37

For each of these rows, I need to find the average of the lowest four of the last six.

Any advice??

**Bob Phillips** · 05-03-2010, 02:20 PM

Try this ARRAY formula

=AVERAGE(SMALL(OFFSET($A$1,0,LARGE(IF(ISNUMBER($A1:N1),COLUMN($A1:$N1)),6)-1,1,6),{1,2,3,4}))

**JBeaucaire** · 05-03-2010, 02:27 PM

Give this a try:

Please Login or Register  to view this content.

**JBeaucaire** · 05-03-2010, 02:29 PM

Tip of the hat to BOB...great formula.

I had to tweak some of the $ signs out to so it copied down correctly:

=AVERAGE(SMALL(OFFSET(A1, 0, LARGE(IF(ISNUMBER(A1:N1), COLUMN($A1:$N1)), 6) - 1, 1, 6), {1,2,3,4}))

**DonkeyOte** · 05-03-2010, 04:18 PM

If you make the same assumptions re: contiguous data set and always 6 values then you could achieve the same I think per the below

=AVERAGE(SMALL($N1:INDEX($A1:$N1,MATCH(9.99E+307,$A1:$N1)-5),{1,2,3,4}))

confirmed with ENTER

If you need to account for non-contiguous data sets and/or < 4 / 6 results then obviously the above (like Bob's) would require adaptation.

**Bob Phillips** · 05-03-2010, 04:45 PM

I did alloow for non-contiguous data Luke <g>

**DonkeyOte** · 05-03-2010, 05:43 PM

Hi Bob,

the starting point for the Average range would adjust for non-contiguous data points however the resulting range itself would not

The Average range is hardwired to 1 x 6 from variable start point it follows that if any of the 5 subsequent data points were non-contiguous then in present form the Average would not be extracting 4 values from 6 numerics
(there would be non-numerics interspersed within the 1 x 6)

For the record I wasn't advocating my approach over yours - just illustrating an alternative based on similar assumptions.