Match and Index

[jdeumer]

L.S.,

For my thesis I have sorted stocks into quintiles based on an defaul
indicator and calculated the average as following:

For Q1:
=AVERAGE(LARGE($B14:$FT14,ROW(A$1:OFFSET(A$1,$FW14/5-1,0,1))))
For Q2:
=AVERAGE(LARGE($B14:$FT14,ROW(OFFSET(B$1,$FW14/5,0,1):OFFSET(B$1,2*$F
W14/5-1,0,1))))
…
For Q5:
=AVERAGE(LARGE($B14:$FT14,ROW(OFFSET(E$1,4*$FW14/5,0,1):OFFSET(E$1,$F

14-1,0,1))))

(all array functions)

where
$B14:$FT14 = the range with all available stocks and
the ROW function indicates the range for “k”: since the number o
available stocks differs per month, the value for "k" (in this case
range) needs to be adapted every time.
Some more details are included in this formula considering the roundin
of the number of stocks per portfolio. This is however not relevant fo
my question.

Perhaps not too elegant, it works perfectly. The next challenge i
matching returns to the indicator for default risk. Every observatio
of the indicator has a matching average return, stated elsewhere in th
sheet. Now I need to sort the stocks based on the indicator (as above
and calculate the average return for the portfolio.

So if Q1 consists of stocks 2,4,7 and 8 for example, the average retur
for Q1 should consist of the average of the returns for stocks 2,4,7 an
8. How can I make Excel do this? By using INDEX and MATCH? I tried thi
with the unsuccessful result:


{=AVERAGE(INDEX(B902:FT902,MATCH(LARGE($B14:$FT14,ROW(A$1:OFFSET(A$
1,$FW14/5-1,0,1)))),$B14:$FT14,0))}

If somebody can help, I’d be very grateful!

Thanking you in advance,

RJE Deumer