Ranking with Sumproduct Problem with #N/A

**mbshafe** · 01-27-2016, 02:26 PM

I have two ranges of data - here is the first:

-3%
-11%
-10%
-6%
-15%
98%
-16%
-11%
0%
-19%
-4%
-3%

I am able to successfully rank this range based on the absolute value of each data point using the following formula:

=SUMPRODUCT(--(ABS(A$1:A$12)>ABS(A1)))+1

Here's my second range:

#N/A
-6%
#N/A
#N/A
-9%
62%
-11%
-6%
-17%
-12%
#N/A
#N/A

I am able to rank this range and ignore the #N/A values using the following formula:

=IF(ISNA(B1),"",COUNTIF(B$1:B$12,">"&B1)+1)

The problem is that I need to also rank the second range based on absolute values and I can't figure out how to modify either formula to do it. Any suggestions?

TIA

**ConneXionLost** · 01-27-2016, 02:36 PM

Try this array formula:

=SUMPRODUCT(--(IF(ISERROR(A$1:A$12),0,ABS(A$1:A$12))>IF(ISERROR(A1),0,ABS(A1))))+1

Confirm as an array formula with Ctrl-Shift-Enter.

Cheers,

**Kaper** · 01-27-2016, 02:46 PM

Try such array formula:

Formula:

=IF(ISERROR(A1),"",SUM(--(ABS(IF(ISERROR(A$1:A$12),0,A$1:A$12))>ABS(A1)))+1)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.