L.S.,

For my thesis I have sorted stocks into quintiles based on an default
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,$FV

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 of
available stocks differs per month, the value for "k" (in this case a
range) needs to be adapted every time.
Some more details are included in this formula considering the rounding
of the number of stocks per portfolio. This is however not relevant for
my question.

Perhaps not too elegant, it works perfectly. The next challenge is
matching returns to the indicator for default risk. Every observation
of the indicator has a matching average return, stated elsewhere in the
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 return
for Q1 should consist of the average of the returns for stocks 2,4,7 and
8. How can I make Excel do this? By using INDEX and MATCH? I tried this
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.


--
jdeumer
------------------------------------------------------------------------
jdeumer's Profile: http://www.excelforum.com/member.php...o&userid=25181
View this thread: http://www.excelforum.com/showthread...hreadid=387098