Ranking with N/A values

**ndw123** · 02-05-2013, 09:49 AM

Hello,

For my paper I'm trying to calculate the rank of stock returns over a 262 day period. For most of my returns this is no problem and can be computed using the regular RANK function. However, some companies don't have 262 returns over this period, which causes the formula to stop working due to the #N/A values. It would be great if you guys can create a formula that ranks the returns that the company does have over this period (i.e. if the company has 150 returns over the 262 day period, just rank these from 1 to 150). I tried adapting the formula myself, but that didn't realy work out.

Thanks in advance!

**daddylonglegs** · 02-05-2013, 09:55 AM

Try using COUNTIF to rank as that function can ignore #N/A values in the data, e.g. with data to rank in A2:A263 try this formula in B2 copied down

=IF(ISNA(A2),"",COUNTIF(A$2:A$263,">"&A2)+1)

ranks highest value 1 - change > to < to rank lowest value 1