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.


On May 14, 9:28 am, Pradeep wrote:
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?