Hi
I'm trying to find the 25,50 and 75 percentile of numbers below 0, in a row with both negative and positive numbers.
This is the array formula that I try, but it only returns the value 0...
=PERCENTILE.EXC(ABS(IF(Day!AZ20:Day!AZ2000<0,Day!AZ20:Day!AZ2000)),0.25)
Use this, still entered as an array formula:
Formula:
Please Login or Register to view this content.
PERCENTILE.EXC is treating all positive values as zero, not ignoring them -- you left the last term of the IF implicit, so it's returning FALSE, which is being forced to zero by ABS, which is being accepted as a valid argument by the percentile function.
Try nesting like this:
Please Login or Register to view this content.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
I don't know why that worked nowbut that formula without ABS I tried many times... apparently it works now, lol
Thanks for the help
hmmm.... I'm not sure which one I will use now:Originally Posted by ben_hensel
PERCENTILE.EXC is treating all positive values as zero, not ignoring them -- you left the last term of the IF implicit, so it's returning FALSE, which is being forced to zero by ABS, which is being accepted as a valid argument by the percentile function.
Try nesting like this:
Please Login or Register to view this content.
With the first one =PERCENTILE.EXC(IF(Day!AZ20:Day!AZ2000<0,Day!AZ20:Day!AZ2000),0.25)
I get: -2.36
with yours {=PERCENTILE.EXC(IF(D16:D50<0,ABS(D16:D50)), 0.25)}
I get: 0.73
What I want is a negative number, as I want to find what 25% percentile is (meaning 25% of the values are -2.36 or higher(?) (or would it be lower now, as it's a negative number, I'm confused
When I was experimenting with your formula, I noticed that, in the case where a bunch of zeros values weren't driving it to zero (ie I fed it mostly negative numbers), the ABS was nested such that all the numbers were reflected across the origin and assessed as positive. I assumed that was what you wanted. But if you just want to find the percentile of negative numbers, then no, you don't even want the ABS to be in there.
okay, thanks, then its the first oneAnd If I'm correct, then 25% percentile of a negative number, ex. -5, means that 25% of the numbers are between -5 and 0?
No.
PERCENTILE.EXC( {-1, -2, -3, -4, -5}, 0.25) = -4.5
It's the 25th percentile from the left (negative) end of the numberline.
I think you may want something like:
Please Login or Register to view this content.
I tried all these but doesn't works
=ArrayFormula(PERCENTILE(if(F$13:F$20<0,F$13:F$20),0.1))
I tried this and its working
maniacs205, please post a new thread if you have a new question.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks