percentrank.....if?
Posted by John A. McGraw on November 20, 2001 10:57 PM
I am using the percentrank function to determine the historical probability of a data occuring in a series.
I want to be able to set conditions on which data in the array the percentrank function will include.
Ideally it would be similiar to the way the "sumif" function works. Unfortunately there is no "percentrankif" function. :)
Is there anyway I can do this?
Thanks for any help...
Posted by Mark W. on November 21, 2001 8:03 AM
> I want to be able to set conditions...
Describe your conditions.
Posted by John A. McGraw on November 21, 2001 1:02 PM
To illustrate:
Lets say there are children ranging from ages 10 to 15, their ages are in column A1:A100.
Their height is listed in B1:B100.
If I want to know the rank of 5 feet tall kids in the series I do:
percentrank(b1:b100,5)
But what if I want to know the rank of 5 feet tall kids among only the 10 year old kids?
Idealy I would use this "imaginary" function "percentrankif"... I would do it like this: (Using the same format as sumif())
percentrankif(a1:a100,"=10",b1:b100,5)
So from a1:a100 the condition "= 10" would be used to isolate all the 10 year old kids, and in b1:b100 the percentrank would only include kids who's A Column says they are 10 years old. 5 (feet tall) is the number to find the rank of.
Any ideas on how I could get these results?
Maybe if I had the VBA code to percentrank, I could modify it to allow conditions... But I'm not much of a programmer, so I wouldnt know unless I tried. :)
Posted by Aladin Akyurek on November 22, 2001 7:44 AM
-----------------------------
John,
Try:
=SUMPRODUCT((A1:A100=10)*(B1:B100<5))/(SUMPRODUCT((A1:A100=10)+0)-1)
Aladin
Posted by Aladin Akyurek on November 22, 2001 3:58 PM
ERR
Posted by Aladin Akyurek on November 22, 2001 4:01 PM
Typo Fix
can be a bit simplified: =SUMPRODUCT((A1:A100=10)*(B1:B100<5))/(COUNTIF((A1:A100,10)-1)
It should be:
=SUMPRODUCT((A1:A100=10)*(B1:B100 < 5))/(COUNTIF(A1:A100,10)-1) Aladin ========
