Ignore #N/A when ranking

[Celticshadow]

Hi All

I have the below figures in column N. I am using the below formula to rank the figures in column N but it is returning #N/A in each corresponding cell in column M,thus how do I get the formula to ignore the error #N/A and rank the other figures.


=SUMPRODUCT((N4> N$4:N$15)/COUNTIF(N$4:N$15,N$4:N$15))+1

18.50
0.83
14.50
68.00
57.00
46.00
66.00
92.00
39.00
#N/A
14.00
#N/A


Thanks in advance.

Kind Regards

 

[barry houdini]

Try switching to an array formula....

=IF(ISNUMBER(N4),SUM(IF(ISNUMBER(N$4:N$15),(N4>N$4:N$15)/COUNTIF(N$4:N$15,N$4:N$15)))+1,"")

confirmed with CTRL+SHIFT+ENTER

 

[Celticshadow]

Try switching to an array formula....

=IF(ISNUMBER(N4),SUM(IF(ISNUMBER(N$4:N$15),(N4>N$4:N$15)/COUNTIF(N$4:N$15,N$4:N$15)))+1,"")

confirmed with CTRL+SHIFT+ENTER

Hi Barry Houdini

Many thanks that works perfectly. Another super fast solution done with the minimum of fuss, quality pure quality.

Kind Regards

 

[pgc01]

Hi

Another option, non-array, try:

=IF(ISNUMBER(N4),SUMPRODUCT(COUNTIF(N4,">"&N$4:N$15)/COUNTIF(N$4:N$15,N$4:N$15))+1,"")

 

[barry houdini]

.....now I was thinking....COUNTIF will ignore errors......but I didn't take that extra step, nice one pgc

 

[bncoxuk]

Hi

Another option, non-array, try:

=IF(ISNUMBER(N4),SUMPRODUCT(COUNTIF(N4,">"&N$4:N$15)/COUNTIF(N$4:N$15,N$4:N$15))+1,"")

Surprisingly, this formula does not work

 

[barry houdini]

works OK for me.....

The formula in question ranks "without gaps" and requires the whole range to be populated. Why do you think it doesn't work, in what way?

 

[bncoxuk]

works OK for me.....

The formula in question ranks "without gaps" and requires the whole range to be populated. Why do you think it doesn't work, in what way?

can you attach an excel file to show this as an example?

 

[barry houdini]

Try this

 

[bncoxuk]

Try this

=IF(ISNUMBER(N4),COUNTIF($N$4:$N$15,"<="&N4)-1,"") and then copy down.