software

I am trying to determine the appropriate equation for my application

I have the following:

=IF($D2=quot;C-Dquot;,RANK($F2,(IF($D$2:$D$106=quot;C-Dquot;,IF(ISNUMBER($F$2:$F$106),$F$2:$F$106))),0),NA() )

What I want to do is Rank the values in F2:F106 if the values in D2106 =
quot;C-Dquot;. I have committed this with CTRL SHIFT ENTER.

I get NA in the right places, and a VALUE error if the value in D2106 =
quot;C-Dquot;.

Thanks.

Barb Reinhardt

You probably have to create a help column like

=IF(D2=quot;C-Dquot;,F2,quot;quot;)copy down, assume you put that formula in G2

then use

=IF(G2=quot;quot;,NA(),RANK(G2,$G$2:$G$106))

copy down

RANK cannot be used as an array formula but it ignores blanks so it can be
used as above

Regards,

Peo Sjoblom
quot;Barb Reinhardtquot; wrote:

gt; I am trying to determine the appropriate equation for my application
gt;
gt; I have the following:
gt;
gt; =IF($D2=quot;C-Dquot;,RANK($F2,(IF($D$2:$D$106=quot;C-Dquot;,IF(ISNUMBER($F$2:$F$106),$F$2:$F$106))),0),NA() )
gt;
gt; What I want to do is Rank the values in F2:F106 if the values in D2106 =
gt; quot;C-Dquot;. I have committed this with CTRL SHIFT ENTER.
gt;
gt; I get NA in the right places, and a VALUE error if the value in D2106 =
gt; quot;C-Dquot;.
gt;
gt; Thanks.
gt;
gt; Barb Reinhardt

Maybe...

G2, copied down:

=IF(D2=quot;C-Dquot;,SUMPRODUCT(--($D$2:$D$106=D2),--(F2lt;$F$2:$F$106)) 1,#N/A)

Hope this helps!

In article gt;,
Barb Reinhardt gt; wrote:

gt; I am trying to determine the appropriate equation for my application
gt;
gt; I have the following:
gt;
gt; =IF($D2=quot;C-Dquot;,RANK($F2,(IF($D$2:$D$106=quot;C-Dquot;,IF(ISNUMBER($F$2:$F$106),$F$2:$F$
gt; 106))),0),NA())
gt;
gt; What I want to do is Rank the values in F2:F106 if the values in D2106 =
gt; quot;C-Dquot;. I have committed this with CTRL SHIFT ENTER.
gt;
gt; I get NA in the right places, and a VALUE error if the value in D2106 =
gt; quot;C-Dquot;.
gt;
gt; Thanks.
gt;
gt; Barb Reinhardt