Percentile by unique location

[jw01]

all

I have list of values in column O. in column I, location are listed.

in cell P14 down, i would like a formula that would bucket the values based on "25th, 75th and 90th" percentile (values in cell P10:P12) ...exclude 0s

For for example, in cell P14, it should say "25th" as that's the percentile but has to be unique to the location, listed in column I.

column I has tons of different cities, so is it possible for the formula to automatically adjust if the city changes? example, in cell P43, the location becomes australia, so i would like the percentile bucket to be unique to australia?

i have attached workbook...can someone pls assist? thxs

[jw01]

any thoughts on this guys?

[XLent]

by replying to your own post I suspect a few will have missed this (as often search for unanswered)

not sure I understand the requirement but, in an effort to get it moving, is below along the right lines?

Please Login or Register  to view this content.

I believe PERCENTRANK is compatible with both 2003 & 2007.

[jw01]

Hi there

this works great ....just wondering if an edit can be made?

The above formula aggregates the data into those percentile buckets (25th, 75th and 90th) based on where the number lands in the specific location.

Is it possible to have the percentile for the formula to do the following (excluding 0s and blanks):
ie. 100 people in an office, you would apply the 25th percentile number to the bottom 33 people; the median (50% percentile) number to the middle 33 people and the 75th percentile number to the top 34 people? - can this formula be unique by location?

thxs so much

[XLent]

please provide an updated sample (per post #1) illustrating expected results for a number of, (not all), locations.

the answer will be "yes" - but I'd prefer to not guess as to what your results should look like
i.e. are you applying the respective percentile value to a 1/3 of population relative to their rank, or simply tagging them?

[jw01]

Hey there

Pls see attache file

as you can see in column Q, i have added what i think should be the entry "25th, 50th and 90th". blanks and 0s should be "", however all other values should be "25th, 50th and 90th". I only showed this for Amsterdam.

Basically, if a location has 100 ppl, bottom 1/3 will get 25th percentile, middle 1/3 will get 50th and top 1/3 will get 90th.

is that doable? thx so much!

[jw01]

please provide an updated sample (per post #1) illustrating expected results for a number of, (not all), locations.

the answer will be "yes" - but I'd prefer to not guess as to what your results should look like
i.e. are you applying the respective percentile value to a 1/3 of population relative to their rank, or simply tagging them?

Yes, i would agree would your statement:
i.e. are you applying the respective percentile value to a 1/3 of population relative to their rank

[JeteMc]

Perhaps the following modification to XLent's formula will do what you want:

Please Login or Register  to view this content.

Let us know if you have any questions.