Find average of last 24 hrs data from a filtered range based on a criteria

[styldude]

Can someone help to to find the average of last 24 hrs data from a filtered range based on a criteria.

I have date/time stamp in column A, and data X & Y in column in B & C respectively. Column D has the sum of X & Y value. Now i want to filter the data based on "X" (X>250). From this filtered range I want the find the approximate last 24 hours average of column D data ("C") automatically using formula. This value might change if I change the filter range (say >100), dynamic 24 hrs calculation based on visible cells only. I tried subtotal but had to update the reference manually every time after filtering. Is there a formula to do this automatically.

I have attached the sample file. Please help.

[JohnTopley]

See attached:

I used formulae to find Start and End dates based on selection of "filter" value in C3

End date in F5:

=LARGE(IF($B$5:$B$1000>C3,($A$5:$A$1000)*($B$5:$B$1000>C3),""),1)

Start Date in F6

=SMALL(IF(($B$5:$B$1000>$C$3)*($A$5:$A$1000>=$F$5-1),($B$5:$B$1000>$C$3)*($A$5:$A$1000>=$F$5-1)*($A$5:$A$1000),""),1)

both ...
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in F8

=AVERAGEIFS($C$5:$C$1000,$A$5:$A$1000,">=" &$F$6,$A$5:$A$1000,"<=" & $F$5,$B$5:$B$1000,">" &$C$3)