Sumproduct Question
 

I am trying to determine the appropriate equation for my application

I have the following:

=IF($D2="C-D",RANK($F2,(IF($D$2:$D$106="C-D",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 D2:D106 =
"C-D". I have committed this with CTRL SHIFT ENTER.

I get NA in the right places, and a VALUE error if the value in D2:D106 =
"C-D".

Thanks.

Barb Reinhardt

Sumproduct Question
 

You probably have to create a help column like

=IF(D2="C-D",F2,"")


copy down, assume you put that formula in G2

then use

=IF(G2="",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






"Barb Reinhardt" wrote:

I am trying to determine the appropriate equation for my application

I have the following:

=IF($D2="C-D",RANK($F2,(IF($D$2:$D$106="C-D",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 D2:D106 =
"C-D". I have committed this with CTRL SHIFT ENTER.

I get NA in the right places, and a VALUE error if the value in D2:D106 =
"C-D".

Thanks.

Barb Reinhardt

Sumproduct Question
 

Maybe...

G2, copied down:

=IF(D2="C-D",SUMPRODUCT(--($D$2:$D$106=D2),--(F2<$F$2:$F$106))+1,#N/A)

Hope this helps!

In article ,
Barb Reinhardt wrote:

I am trying to determine the appropriate equation for my application

I have the following:

=IF($D2="C-D",RANK($F2,(IF($D$2:$D$106="C-D",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 D2:D106 =
"C-D". I have committed this with CTRL SHIFT ENTER.

I get NA in the right places, and a VALUE error if the value in D2:D106 =
"C-D".

Thanks.

Barb Reinhardt