Rank Formula not working in Excel 2007

**jcutler0902** · 11-12-2010, 02:51 PM

I have a list of 22 customers and their associated ranks in the adjacent column.

For some reason the rank for the very first customer is not working ( # N/A). The formula I am using is: =RANK(N6,$N$7:$N$28,0)+COUNTIF($N$7:N29,N6)-1

As you can see the formula (or it seems) to be working fine on the other lines.
*Col N is based on =SUM(B6:M6) (copied all the way down)
*Col O is based on =RANK(N6,$N$7:$N$28,0)+COUNTIF($N$7:N29,N6)-1

One thing to make mention, I have copied this entire spreadsheet from a previous year to get the basic format setup.

Please see attached.

Thanks for the help.
Jason

**martindwilson** · 11-12-2010, 02:59 PM

your formula in O4 Should be
=RANK(N4,$N$4:$N$25,0)+COUNTIF($N$4:N25,N4)-1

**daddylonglegs** · 11-12-2010, 03:01 PM

In the attached sheet it seems that the data to rank is in N4:N25 in which case you probably need this formula in O4 copied down

=RANK(N4,$N$4:$N$25,0)+COUNTIF($N$4:N4,N4)-1