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.

 

[BWMagee]

I should note that when I don't run any filters it actually gives the correct result. It's only when I apply a filter that it gives "#DIV/0!"

 

[Tetra201]

Assuming your range A2:A100 has sequential numbers from 2 to 100, try naming this range as "Nums" and see if the following formula works for you:

=(INDEX(Score,AGGREGATE(14,5,Nums,1)-1)+INDEX(Score,AGGREGATE(14,5,Nums,2)-1)+INDEX(Score,AGGREGATE(14,5,Nums,3)-1))/3

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(IF(SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)),ROW(Score))>=LARGE(IF(ISNUMBER(Score),IF(SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)),ROW(Score))),MIN(3,SUM(IF(SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)),1)))),IF(ISNUMBER(1/SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1))),SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)))))

Note that Score stands for B2:B100. As such, it's available in the Name Manager. If you also define FilteredScore in the Name Manager as referring to:

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

we can shorten the main formula considerably. Again, control+shift+enter, not just enter:

=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)))

 

[BWMagee]

Assuming your range A2:A100 has sequential numbers from 2 to 100, try naming this range as "Nums" and see if the following formula works for you:

=(INDEX(Score,AGGREGATE(14,5,Nums,1)-1)+INDEX(Score,AGGREGATE(14,5,Nums,2)-1)+INDEX(Score,AGGREGATE(14,5,Nums,3)-1))/3

Thanks again Tetra! This is very close, but not quite there. Here's what it's doing:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Nums[/TD]
[TD]Name[/TD]
[TD]Rd[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Dave[/TD]
[TD]1[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Dave[/TD]
[TD]2[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Dave[/TD]
[TD]3[/TD]
[TD]58[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Dave[/TD]
[TD]4[/TD]
[TD]76[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Dave[/TD]
[TD]Totals[/TD]
[TD]213[/TD]
[/TR]
</tbody>[/TABLE]

LAST THREE: 55

Note: Although '213' is the sum of scores above it, it is a value not a formula, as I have copied it as a value from another worksheet.

So your formula is giving me the Average of Nums 9-7, instead of 10-8. Given the 'Rd' column doesn't feature in your formula, I can't imagine that word 'totals' would throw it out?

When I filter the 'Totals' row out, it gives LAST THREE: 45.667. (Should be 55).

 

[BWMagee]

Control+shift+enter, not just enter:

=AVERAGE(IF(IF(SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)),ROW(Score))>=LARGE(IF(ISNUMBER(Score),IF(SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)),ROW(Score))),MIN(3,SUM(IF(SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)),1)))),IF(ISNUMBER(1/SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1))),SUBTOTAL(9,OFFSET(INDEX(Score,1,1),ROW(Score)-ROW(INDEX(Score,1,1)),0,,1)))))

Thanks for the reply Aladin. A strange thing happens with this one. It initially gives the correct answer. But then if I type in any other cell, it immediately adjusts back to an answer of '8.6667' - which is the Last Three average of the unfiltered table. Those cells I'm typing into are not part of the "Score" named range either.

 

[Tetra201]

...When I filter the 'Totals' row out, it gives LAST THREE: 45.667. (Should be 55).

Looks like your "Nums" column starts with 1 in cell A2, not with 2 as suggested in Post #3. If this is the case, try this modified formula:

=(INDEX(Score,AGGREGATE(14,5,Nums,1))+INDEX(Score,AGGREGATE(14,5,Nums,2))+INDEX(Score,AGGREGATE(14,5,Nums,3)))/3

 

[Aladin Akyurek]

Thanks for the reply Aladin. A strange thing happens with this one. It initially gives the correct answer. But then if I type in any other cell, it immediately adjusts back to an answer of '8.6667' - which is the Last Three average of the unfiltered table. Those cells I'm typing into are not part of the "Score" named range either.

What do you mean by any other cell? The formula should be placed outside the data area.

By the way, if you have named range Score and you define FilteredScore as I have stipulated, you can use the formula set up in terms of FilteredScore, which is easier to manage.

 

[BWMagee]

Looks like your "Nums" column starts with 1 in cell A2, not with 2 as suggested in Post #3. If this is the case, try this modified formula:

=(INDEX(Score,AGGREGATE(14,5,Nums,1))+INDEX(Score,AGGREGATE(14,5,Nums,2))+INDEX(Score,AGGREGATE(14,5,Nums,3)))/3

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

What do you mean by any other cell? The formula should be placed outside the data area.

By the way, if you have named range Score and you define FilteredScore as I have stipulated, you can use the formula set up in terms of FilteredScore, which is easier to manage.

The formula was outside the data area, yes. By any other cell, I tested numerous different cells outside the data area. It didn't matter whether that cell was one down, one up, two left, three right, five up - as soon as I typed in another cell the formula changed from averaging last three of the filtered data, to last three of unfiltered data.

Not to worry, the problem has now been solved, and I appreciate the response!

 

[Aladin Akyurek]

[...]
The formula was outside the data area, yes. By any other cell, I tested numerous different cells outside the data area. It didn't matter whether that cell was one down, one up, two left, three right, five up - as soon as I typed in another cell the formula changed from averaging last three of the filtered data, to last three of unfiltered data.

Not to worry, the problem has now been solved, and I appreciate the response!

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.