Percentile with multiple conditions from data in another sheet

Hi,

I'm trying to work out the percentile of a set of data based on multiple conditions.

I have a sheet containing over 100 rows of data and want to work out the percentile of those matching rows where a date is greater than or equal to one date and less than or equal to another.

E.g Sheet 1 has multiple columns but the column which contains the data to use for the percentile is in column O. The date I'm using to match my conditions is in column M.

The dates I'm comparing them against are in another sheet (Sheet 2) in cells D2 and E2.

What I've tried so far is the following (using just the date in D2):

Please Login or Register  to view this content.

I'm only trying to match the first date for now, to get it right, before I try and match against multiple conditions.

When searching for an answer, I've only found the criteria using =, rather than >=, therefore not sure if my syntax is correct?

Thanks

Thanks for your reply.

I've tried your suggestion but I'm just getting 0. However, I'm not sure the CTRL + SHIFT + ENTER command has worked. I'm on a Mac, so would that be CMD + SHIFT + ENTER? Also, which cell are you referring to? I guess the one where I've entered the formula?

Thanks

Originally Posted by ferguson9

Thanks for your reply.

I've tried your suggestion but I'm just getting 0. However, I'm not sure the CTRL + SHIFT + ENTER command has worked. I'm on a Mac, so would that be CMD + SHIFT + ENTER? Also, which cell are you referring to? I guess the one where I've entered the formula?

Thanks

Ignore that, I've just realised my Mac does actually have a Control key.

Does this work if any cells are blank, or do they have to be 0?

Thanks

I've just done a few more tests on my example spreadsheet and I think it's working without having to specify to exclude the blank cells.

The only problem I have now is that my results are slightly different to an online percentile calculator I'm using. I'm getting 8.6 for 90% percentile, whereas the online calculator returns 9, is that just simply rounding it up? (25%, 50% and 75% return the same values as mine).

E.g using the following data;

2, 2, 5, 2, 11

I get 8.6 for 90% percentile.
Online calculator gives 9 for 90% percentile.

75% returns 5
50% returns 2
25% returns 2

It is an extrapolation to 90%

75%=5
100%=11

90%= 5+ 15/25(6) 90% is 3/5ths of the way between 75% and 100% so 3/5ths of the difference= (11-5)*0.6 =3.6

=5+3.6 =8.6

You get funny results with percentiles when there are more percentiles than their are data points! splitting into 10 when you have 5 day points!


but it is correct and the other program may be rounding errors

Originally Posted by davsth

It is an extrapolation to 90%

75%=5
100%=11

90%= 5+ 15/25(6) 90% is 3/5ths of the way between 75% and 100% so 3/5ths of the difference= (11-5)*0.6 =3.6

=5+3.6 =8.6

You get funny results with percentiles when there are more percentiles than their are data points! splitting into 10 when you have 5 day points!


but it is correct and the other program may be rounding errors

I won't pretend I understand all that but I'll look into how percentiles are worked out, for sure!

Most importantly right now is that the formula is correct, so thanks for confirming