I'm currently working with excel, trying to get back the top scoring returns from shares traded on a stock index. Since this index has changed a lot over time, I put all shares on the same sheet, named the rows by date and the columns by shares. What I need to do is get excel to return values that lie within a certain percentile on a new sheet. I do this by using the following formula:

=IF('Monthly performance'!D24>=PERCENTILE.EXC('Monthly performance'!$B$24:$MB$24;0.7);'Monthly performance'!D24;"")

This works well as long as all the values in a row are directly next to one another, but if a few cells are blank, excel counts these cells as if they are zero skewing the percentile distribution. In the sheet from where I get the data (where the blanks exist) I use an Iferror formula that gives "" Iferror. that may be the problem. Although I have tried to copy paste values (so the blanks really are blanks) but the percentile function still gets skewed.

Please see attached.Momentum Excelhelp.xlsx

Thanks for helping me out!