Hi, I am looking to average the last three values of a filtered table. The table has 100 rows of data. There is a named range "Score" from B2:B100, and when applying the filter I am left with just four values. These four looks like:
<tbody>
</tbody>
So the answer I am looking for is 51 ((82+51+20)/3). The formula I am currently trying to use is this:
=AVERAGE(OFFSET(Ref, Row, Col, Height))
Here is my thought process:
- AVERAGE is SUBTOTAL(101,OFFSET) ["SUBTOTAL" being necessary to only include the filtered items]
- Ref is B1 [Anchor point]
- Row is COUNTA(Score) is SUBTOTAL(103,Score) [Gives the result 4, ie. taking me 4 rows down from the Anchor Point.]
- Col is 0 [Formula is being placed in same column (B) as data. Note: The formula is outside the named range "Score"]
- Height is -3 [As I want the last three results, I go 3 places up from the 4 places down I went in row]
Therefore the combined formula reads like this:
=SUBTOTAL(101,OFFSET(B2,SUBTOTAL(103,Score),0,-3))
And the result I get is:
#DIV/0!
Please help!
PS. If there is a better way to do it than the OFFSET command, I am open to ideas.
A | B |
1 | Score [Header Name] |
9 | 26 |
12 | 20 |
65 | 51 |
82 | 82 |
<tbody>
</tbody>
So the answer I am looking for is 51 ((82+51+20)/3). The formula I am currently trying to use is this:
=AVERAGE(OFFSET(Ref, Row, Col, Height))
Here is my thought process:
- AVERAGE is SUBTOTAL(101,OFFSET) ["SUBTOTAL" being necessary to only include the filtered items]
- Ref is B1 [Anchor point]
- Row is COUNTA(Score) is SUBTOTAL(103,Score) [Gives the result 4, ie. taking me 4 rows down from the Anchor Point.]
- Col is 0 [Formula is being placed in same column (B) as data. Note: The formula is outside the named range "Score"]
- Height is -3 [As I want the last three results, I go 3 places up from the 4 places down I went in row]
Therefore the combined formula reads like this:
=SUBTOTAL(101,OFFSET(B2,SUBTOTAL(103,Score),0,-3))
And the result I get is:
#DIV/0!
Please help!
PS. If there is a better way to do it than the OFFSET command, I am open to ideas.