# Percentrank With an If Statement

1. ## Percentrank With an If Statement

Hi Fellas,

Been a while and glad to be back on the forums (unfortunately have not done a lot of excel recently )

I have two columns with the following data set:
Column A Column B Column C
0 11.5
0 13.5
0 12.2
1 14.0
1 7
1 12
0 2
0 3
0 9.6
1 2.3

I want to run a percent rank function in column C where I am only calculating the percent rank on numbers which have a 1 associated with them in Column A. So for example, the first number 11.5 would have a zero next to it in column C... all of the numbers until the fourth number would have a zero next to them. Then the 4th number would have a value of 1 (because its the largest number)... the last number (2.3) would have a value of 0 as its in the zero percentile. The numbers in between would correspond to the percentile in which they lie (i.e. numbers 7 and 12, roughly .33 and .66 respectively). Any thoughts for how to do this in Column C?

Thanks!

Larry  Register To Reply

2. ## Re: Percentrank With an If Statement

like this
in C1 copy paste below then hold control and shift together then hold control and shift together and then hit enter to make it array formula and drag down
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: Percentrank With an If Statement

with a helper column and non array formula

C1: =IF(A1=1,B1,"")
D1: =IFERROR(RANK.EQ(IF(A1=1,B1,""),\$C\$1:\$C\$10,1)/(COUNT(\$C\$1:\$C\$10)-1)-1/(COUNT(\$C\$1:\$C\$10)-1),"")

C1:D1 copied down  Register To Reply

4. ## Re: Percentrank With an If Statement Originally Posted by hemesh like this
in C1 copy paste below then hold control and shift together then hold control and shift together and then hit enter to make it array formula and drag down
Formula:  `Please Login or Register  to view this content.`
Thanks Hemesh - for some reason it counts the final value as not 1 (but a smaller number say 0.9115) using this approach. I tried changing the formula to percentrank.inc and still same result. Any thoughts?  Register To Reply

5. ## Re: Percentrank With an If Statement

Two thoughts
1. Try the following array entered formula*: =IF(a1=0,"",PERCENTRANK.INC(IF(A\$1:A\$10=1,B\$1:B\$10),B1))
2. PERCENTRANK.INC was introduced in the 2010 version of Excel (please update your profile)
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 