Getting average of top 1/3, second 1/3, and last 1

Question

I have a column with numbers and a reference column. I'm trying to separate the numbers column into first third, second third, and last third and take the average of each.

Values  Ref column
 1.7     cow
 2.3     cow
 2.6     cow
 1.8     sheep
 1.3     sheep
 2.2     sheep
 1.5     sheep
 1.2     sheep
 2.3     sheep
 1.5     goose
 2.5     goose

So, for example, the average of the first two values for "sheep", second two, and last two. In other words, I want to take the average of each 1/3 of cells adjacent to "sheep".

Answer 1

use a formula like this:

=AVERAGE(INDEX(A:A,MATCH($D$2,B:B,0)+(D3-1)*COUNTIF(B:B,$D$2)/3):INDEX(A:A,MATCH($D$2,B:B,0)+((D3)*COUNTIF(B:B,$D$2)/3)-1))

This does require that the ref column be sorted and like references grouped.

---

This array formula will return the averages even if not sorted:

=AVERAGE(INDEX(INDEX(A:A,N(IF({1},MODE.MULT(IF($B$1:$B$12=$D$2,ROW($A$1:$A$12)*{1,1}))))),N(IF({1},ROW(INDEX(A:A,1+(D3-1)*COUNTIF(B:B,$D$2)/3):INDEX(A:A,D3*COUNTIF(B:B,$D$2)/3))))))

array formula need to be entered with Ctrl-Shift-enter instead of Enter when exiting edit mode.

Answer 2

Add a column to cumulatively count the instances of the word you're looking at, then check that row number in your AVERAGE.

1. C2 = =CountIf($B$2:$B2, $B2) and fill down => values should be {1,2,3,1,2,3,4,5,6,1,2}
2. E1 = sheep
3. E2 = =CountIf($B:$B, $E$1) => 6
4. E3 = {=Average(If(($B:$B = $E$1) * ($C:$C <= $E$2 / 3), $A:$A))} (note this is an array formula, as designated by the {} around it) => 1.55
5. E3 = {=Average(If(($B:$B = $E$1) * ($C:$C > $E$2 / 3) * ($C:$C <= 2 * $E$2 / 3), $A:$A))} => 1.85
6. E3 = {=Average(If(($B:$B = $E$1) * ($C:$C > 2 * $E$2 / 3), $A:$A))} => 1.75

Array formulas, if I remember correctly, are entered the same as normal formulas (don't include the {}, that gets entered automatically), but you press Ctrl (and possibly Shift) with Enter when you finish.

NB - these look at the entire column. You can speed them up by changing $A:$A to $A$2:$A$12 (likewise for $B:$B and $C:$C). Just bear in mind that for any data you append to this list, you'll need to update the formulas; but you can insert data into the middle of the list and it will update them automatically.

Answer 3

Well supposing there were 7 sheep values and you wanted to do a weighted mean (e.g. the first mean would be calculated from the first two sheep plus a third of the third one)?

I have attempted a general solution for this dividing any number of animals into any number of fractions and finding their average values. My approach is to use the elegant overlap formula from @Barry Houdini as used here and work out the overlap between the intervals (in the case of 7 animals divided into 3):

0 to 2.33

2.33 to 4.67

4.67 to 7

and the numbers of the animals

0 to 1

1 to 2

2 to 3

and so on.

In H4

=IF(ROWS($1:1)<=$H$2,ROWS($1:1)/$H$2*COUNTIF(B$2:B$16,$G$2),"")

In G4

=IF(H4="","",H4-COUNTIF(B$2:B$16,$G$2)/$H$2)

The main formula in I4 is

=IF(H4="","",SUM(TEXT(IF(C$2:C$16<H4,C$2:C$16,H4)-IF((C$2:C$16-1)>G4,C$2:C$16-1,G4),"general;\0")
*A$2:A$16*(B$2:B$16=$G$2))/(COUNTIF(B$2:B$16,$G$2)/$H$2))

entered as an array formula.

The fractions can be changed to halves, quarters etc. by changing the number in H2.