Average previous 10 cells using Multiple dimensional Index Match

[voltrader]

Hello all,

Been trying to figure this out to no avail. I am trying to Average the previous 10 cell range from my INDEX,MATCH,MATCH result, my formula is:

AVERAGE(IFERROR(INDEX($C$2:$C$10,MATCH(1,INDEX(($A4=$A$2:$A$100)*($H$2=!$B$2:$B$100),0,1),0)),0), (IFERROR(INDEX($C$2:$C$7982,MATCH(1,INDEX(($A4=$A$2:$A$100)*($D$2=!B$2:$B$100),0,1),0)),0)))

My formula is only yielding the avg of the first, and last cells in the desired range, I am trying to Average all the cells of the range.

Thanks!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGE(IFERROR(INDEX($C$2:$C$100,MATCH(1,INDEX(($A4=$A$2:$A$100)*($H$2=$B$2:$B$100),0,1),0)):INDEX($C$2:$C$100,MATCH(1,INDEX(($A4=$A$2:$A$100)*($D$2=B$2:$B$100),0,1),0)),0))

 

[voltrader]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=AVERAGE(IFERROR(INDEX($C$2:$C$100,MATCH(1,INDEX(($A4=$A$2:$A$100)*($H$2=$B$2:$B$100),0,1),0)):INDEX($C$2:$C$100,MATCH(1,INDEX(($A4=$A$2:$A$100)*($D$2=B$2:$B$100),0,1),0)),0))

Hey, Thanks for the heads up, I will update my info. I tried your suggestion, the value return is 0. Doesn't seem to be working.

 

[Fluff]

Did you confirm it with Ctrl Shift Enter, rather than just Enter?