Hi,
So I have a spreadsheet of weekly opening and closing prices for a stock.
I'm trying to combine a Percentile function with an IF function on a range that has positive and negative values.
Here's my current formula:
=ROUND(PERCENTILE.INC(IF($M:$M=$AB$1,$L:$L,),Y$2+0.01),2)
M column indicates if the week finished up or down.
L column gives the change in value for the week, value positive if week was up, negative if week was down
AB1 is the cell where I select up or down to choose which data set I want to see the values of.
Y2 is the cell where I set the percentage value that I want the Percentile function to calculate.
Current results:
When I set AB1 to Up, this formula appears to work well.
When I set AB1 to Down, this formula returns 0 instead of a negative number.
When I use Alt+Shift+Enter, this formula returns 0 regardless if I have AB1 set to Up or Down.
I tried searching for available solutions on this board and others, but none that I've tried has worked.
Based on one solution I saw, I tried putting in ABS(L:L), but that gave me an error.
Another solution I saw, I tried to put 1-Y2 for negative values, but that returned 0 for me as well.
I'm not sure what I'm doing wrong.
Any help would be greatly appreciated.
Bookmarks