Does this work?
=IFERROR(1-PERCENTRANK.INC(IF(B27:B100<>0,$B$27:$B$100),$A$14,3),"")
This now makes it an array formula
Array formula, use Ctrl-Shift-Enter
Hi,
It seems to me that in order to insert your condition :$B$27:$B$100>0 ...
you will have to use the sumproduct() function ...
HTH
=(SUMPRODUCT(($B$27:$B$100<A14)*($B$27:$B$100>0))/(SUMPRODUCT(($B$27:$B$100<A14)*($B$27:$B$100>0))+SUMPRODUCT(($B$27:$B$100>A14)*($B$27:$B$100>0))))*100
Sorry if I did not express myself properly ...
I meant replace the Percentrank function by its sumproduct() equivalent ...
Without your worksheet, my guess would be you should test the following
Code:=(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))/(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))+SUMPRODUCT(($B$27:$B$100>A14)*($B$27:$B$100>0))))*100
HTH
[COLOR=#333333]=IFERROR(1-PERCENTRANK.INC($B$27:$B$100,$A$14,3),"")[/COLOR]
Sorry if I did not express myself properly ...
I meant replace the Percentrank function by its sumproduct() equivalent ...
Without your worksheet, my guess would be you should test the following
Code:=(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))/(SUMPRODUCT(($B$27:$B$100<a14)*($b$27:$b$100>0))+SUMPRODUCT(($B$27:$B$100>A14)*($B$27:$B$100>0))))*100
HTH
Does anyone have any solutions?
Would be so so grateful