Percentile formula w/ 4 diff logics (multiple criteria)

Hi

Looking for help with percentile aggregation based on 3 different methods.

I have offices listed in column I, column O shows value for each row item.

in column Q, i would like to show, 20th, 40th, 60th, and 80th percentile ranking applied to 25% of each population. So, bottom 25% will get 20th percentile, 25% population will get 40th percentile...top 25% will get 80th (exclude 0s and blanks)

in column R, same as above logic, however, it's based on 17th, 33rd, 50th, 66th and 83rd percentile and based on 20% of population per bucket (exclude 0s and blanks)

in column S, same as above logic as well, however, broken down by 7th, 50th and 25th percentile, and it applies to 1/3 or 33.333% of each bucket. So meaning, bottom 1/3 of population will get 25th percentile ranking, mid 1/3 of population will get 50th percentile, and top 1/3 will get 75th percentile ranking

I have attached the spreadsheet.

column P has this formula - it shows percentile of 90th, 75th and 25th (exclude 0s and blanks), however, if values fall below 25th percentile, it shows "", when i would like for it to show 25th ----> can this formula be modified to use for above renditions?

Please Login or Register  to view this content.

thx

Hi, that seems to work

question however is, it seems some of these columns show "100" as the percentile. is it possible to i.e. cap column Q at 80th? and column R at 83rd and column S at 75th?

hi there

i have attached the file.

it's assigning a percentile amount to 0s - can you see the attached file with the formula i.e. row 16:18 as examples.

thxs so much!

hi Bo_ry

this above formula seems to be working, however, there are instances where if there is a value in column O, it is still not assigning the "lowest" percentile to the amount.

I have attached the file and highlighted in yellow in columns Q:S.

Can you pls let me know if it's possible to add a percentile to any value greater than 0?

thanks