Calculate Percentiles with dynamic ranges

[Pradeep]

I have two worksheets in a workbook - say "Calculation" and "Ppl List". In
the "Calculation" sheet, I need to dynamically calculate percentiles for data
given in "Ppl List".

The "Ppl List" sheet has data for people's ages for different groups of
people, say groups A, B and C. The list is sorted based on Group, i.e. Group
A comes first, then Group B below that and so on. However, the number of
people in these Groups may change dynamically as and when names are removed /
added into the table. Therefore, in the Percentile formula, I cannot keep a
constant reference to an array of cells referring to the age for people in a
particular group as this would be subject to change.

How can I calculate the percentiles dynamically?

 

[Nick Dangr]

Depending on how your sheet is laid out, you might be able to define a
range and use SUMIF perhaps in confunction with a COUNTIF. I pull
data on our sales from our live store database into excel. Whether
the stores have items on hand defines whether they appear in the
list. Our list is broken down by department, so in a way I have a
similar situation to yours. I've used SUMIF a few times selecting the
columns containing the product information and naming them (the whole
column, not just the data).

Just a thought - very abstract and not very descriptive, I apologise.

 

[Pradeep]

Thanks Nick. My problem is that I cannot define those ranges as they keep
changing.

 

[Nick Dangr]

Again, depending on the organization of the sheet you might be able to
get around that....

Are the A B C lists separate tables? or are they in one list with the
A,B,C rank in a single column, sorted based on that rank?

 

[Pradeep]

A, B and C are in one list. The data is sorted based on this column that has
the A, B, C data.

 

[vezerid]

I have two worksheets in a workbook - say "Calculation" and "Ppl List". In
the "Calculation" sheet, I need to dynamically calculate percentiles for data
given in "Ppl List".

The "Ppl List" sheet has data for people's ages for different groups of
people, say groups A, B and C. The list is sorted based on Group, i.e. Group
A comes first, then Group B below that and so on. However, the number of
people in these Groups may change dynamically as and when names are removed /
added into the table. Therefore, in the Percentile formula, I cannot keep a
constant reference to an array of cells referring to the age for people in a
particular group as this would be subject to change.

How can I calculate the percentiles dynamically?

You can use dynamic Named Ranges. To avoid overly complex formulas you
can first use some auxiliary cells.
In PplList: Go to a column far enough to the right and enter the group
codes A, B, C. Say you put these in K1:K3.
One column to the right of A (in L1), enter the following: (ando copy
for 3 rows)

=MATCH(A:A,K1,0)

One more column to the right (M1)
=L2-1

Copy down one cell. In M3 enter a large enough number that the overall
list will never be longer.

menu Insert>Name>Define...
Name: A_Ppl
Refers To: =INDIRECT("A"&PplList!L1&":A"&PplList!M1)

Repeat for B_Ppl (using L2, M2) and C_Ppl (with L3, M3)

Now you can use these names in your PERCENTILE function.

HTH
Kostis Vezerides