Ranking issue, returns #num! and #div/0! for a few rows

**jagerstal** · 04-10-2012, 10:23 AM

Hi,

I've build an excel model for stock returns for European countries and I'm having a strange problem with the rank function.
In cell A2:LO152 contains monthly stock prices and cell A159:LO288 contains their monthly return.

Then in cell A297:LO426 ranks each stock based on previews return. This is the formula in A297:

=RANK(B159;OFFSET($B159;0;0;1;COUNTA($B$5:$XFD$5)))+COUNTIF($B159:B159;B159)-1

where $B$5:$XFD$5 counts amount of stock in the worksheet. I have used this formula for 8 other countries stocks and had no problem what so ever. Now I get #NUM! and #DIV/0! for some of the rows and I can't figure out why.

For example:

Row 336 formula is =RANK(B198;OFFSET($B198;0;0;1;COUNTA($B$5:$XFD$5)))+COUNTIF($B198:B198;B198)-1. It returns no error.

Row 336 formula is =RANK(B199;OFFSET($B199;0;0;1;COUNTA($B$5:$XFD$5)))+COUNTIF($B199:B199;B199)-1
and #NUM! for the whole row. The same problem occur in row 338, 339. In row 340 I get #DIV/0! for the whole row as in 341 and 342.

After row 342 there are no problem until row 402, #NUM! again and row 405, #DIV/0!

I tried to evaluate the formula and the offset, counta and countif works correctly.

I can't figure it out, can someone give some advice please?

**daddylonglegs** · 04-10-2012, 11:08 AM

I would assume that you have those errors in the data, e.g. somewhere in row 198 there's a #NUM! error, somewhere in row 202 there's a #DIV/0! error etc.

find the location of the first error in row 198 with this formula

=ADDRESS(198,MATCH(TRUE,INDEX(ISERROR(198:198),0),0))