Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Sumproduct Question | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
sumproduct question | Excel Worksheet Functions |