Rank function ignoring only zero values

[mfortier3]

Hello,

I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?

=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))

Thanks.​

 

[Aladin Akyurek]

Copied across...

=IF(ISNUMBER(1/D94),RANK(D94,$D$94:$AY$94,0),"")

appears to work.

 

[MUKESHY12390]

hey ... just replaced all 0 with any text or with '0

 

[mfortier3]

Copied across...

=IF(ISNUMBER(1/D94),RANK(D94,$D$94:$AY$94,0),"")

appears to work.

Unfortunately it seems to be producing the same ranking as the original formula.

 

[Marcelo Branco]

Maybe this

=IF(AND(D94<>0,D94<>""),SUMPRODUCT(--($D$94:$AY$94>D94),--($D$94:$AY$94<>0),--($D$94:$AY$94<>""))+1,"")

copy across

 

[Aladin Akyurek]

Unfortunately it seems to be producing the same ranking as the original formula.

Given:

-5,2,#,#,2,7,-2

what would be the expected ranking? # stands for a blank or an empty cell.

 

[mfortier3]

Maybe this

=IF(AND(D94<>0,D94<>""),SUMPRODUCT(--($D$94:$AY$94>D94),--($D$94:$AY$94<>0),--($D$94:$AY$94<>""))+1,"")

copy across

This worked, thanks!

 

[Marcelo Branco]

This worked, thanks!

You are welcome and thanks for the feedback.

M.

 

[Marcelo Branco]

Cross-posted
Rank function ignoring zero and negative values

Forum rules
Rule #10:
We prefer that members do not cross-post questions to other forums, but when this does occur members must make clear that they have cross-posted and must provide links to the cross-posts. Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: http://www.excelguru.ca/content.php?184

M.

 

[mfortier3]

Cross-posted
Rank function ignoring zero and negative values

Forum rules
Rule #10:
We prefer that members do not cross-post questions to other forums, but when this does occur members must make clear that they have cross-posted and must provide links to the cross-posts. Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: http://www.excelguru.ca/content.php?184

M.

Sorry about that. I'll be sure to do that if I ever cross-post in the future.