Subtotal Offset

[BWMagee]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Score [Header Name][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]65[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD]82[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Tetra201]

... Works perfectly now! 3/3 from you, where's the donate button?

Glad to have helped you.

No donate buttons here, but you can "Like" posts that you find useful or neat.

 

[BWMagee]

Control+shift+enter means: Press down the control and the shift keys at the same while you hit the enter key. If done correctly, Excel itself puts a pair of { and } around the formula in recognition.

You are assuming I didn't do that. I did. Didn't work.

 

[Aladin Akyurek]

You are assuming I didn't do that. I did. Didn't work.

Not assuming... It was a case of "just in case". Maybe I have your data wrong. The data I assumed is given in the following workbook:
https://www.dropbox.com/s/9zxiy4sh6l3wsa6/BWMagee Subtotal Offset.xlsx?dl=0

To recap...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]

51​

[/td][/tr]
[tr][td]

2​

[/td][td] x[/td][td] Score [Header Name][/td][/tr]
[tr][td]

3​

[/td][td] a[/td][td] 26[/td][/tr]
[tr][td]

4​

[/td][td] a[/td][td] 20[/td][/tr]
[tr][td]

5​

[/td][td] a[/td][td] 51[/td][/tr]
[tr][td]

7​

[/td][td] a[/td][td] 82[/td][/tr]
[tr][td]

12​

[/td][td][/td][td][/td][/tr]
[/table]

Defintions...

Score:

=Sheet1!$B$3:$B$200 (Adjust the sheet name if needed.)

FilteredScore:

=SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1))

Formula in B1:

=AVERAGE(IF(IF(FilteredScore,ROW(Score))>=LARGE(IF(ISNUMBER(Score),IF(FilteredScore,ROW(Score))),MIN(3,SUM(IF(FilteredScore,1)))),IF(ISNUMBER(1/FilteredScore),FilteredScore)))

which must be confirmed with control+shift+enter, not just with enter.

Note. This formula is easily extendible to say last 10 by changing 3 in MIN to 10.

 

[BWMagee]

I appreciate the response, but sorry to say it isn't working. Using the FilteredScore formula it keeps giving an answer of 41, for both the FilteredScore and for the Formula in B1. It does this even when i change the min to 10, while if I filter other results it brings up a #NUM! error. Tetra's formula works for me anyway, so I have no need to continue down this line.