PDA

View Full Version : Solved: Percentrank :CSE



asingh
11-18-2009, 04:06 AM
Is it possible to use Percentrank as a CSE function.

I have something like this:
=PERCENTRANK(IF($D$3:$D$30=$D$31,TRUNC($C$3:$C$30,2),FALSE),$C$31)

Where $D$3:$D$30=$D$31 is the condition to be evaluated, and TRUNC($C$3:$C$30,2) would be returned.

p45cal
11-18-2009, 05:18 AM
Yes

asingh
11-18-2009, 05:35 AM
I keep getting a #N/A. Would a file help..?

asingh
11-18-2009, 06:08 AM
Am attaching a sanitized file. Still not being able to crack this one...

Cell C33 has the function I have put. Basically I want to be able to choose the metric2 in D31, and only the PercentRank for values with that metric2 should be evaluated.

Bob Phillips
11-18-2009, 06:22 AM
TRy this

=PERCENTRANK(IF($D$3:$D$30=$D$31,TRUNC($C$3:$C$30,2),99^99),$C$31)

p45cal
11-18-2009, 06:22 AM
That's because, in your file, the 0.56 is higher than any value for Metric for any row with the Metric2 of 'B'.
From Help:

If x does not match one of the values in array, PERCENTRANK interpolates to return the correct percentage rank.So in this case there's no match, but also nothing to interpolate.
Either change the 0.56 to a lower value (probably silly), or (more likely to be useful) include the 0.56 in the formula:
=PERCENTRANK(IF($D$3:$D$31=$D$31,TRUNC($C$3:$C$31,2),FALSE),$C$31)
when you get a sensible answer of 1 or 100%

asingh
11-23-2009, 05:25 AM
^^
yes, worked. Thanks to you and XLD for the suggestions....!