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.
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.
Yes
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
I keep getting a #N/A. Would a file help..?
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.
TRy this
=PERCENTRANK(IF($D$3:$D$30=$D$31,TRUNC($C$3:$C$30,2),99^99),$C$31)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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:
So in this case there's no match, but also nothing to interpolate.
- If x does not match one of the values in array, PERCENTRANK interpolates to return the correct percentage rank.
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%
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
^^
yes, worked. Thanks to you and XLD for the suggestions....!