Hi, I have a large series of numbers saved in one column
[34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by
[1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column.

I need to calculate percentiles (25%,50% and 75%) of these numbers for
different indexes. In another word, I need to calculate percentiles for those
numbers with index of 1, 2, ... respectively, not the percentiles of all the
numbers.

Thanks a lot!

Assuming:
Index Percentage to use
1 25%
2 50%
3 75%
and number is in column A and index is in column B on same row, like:
A B
104 1
103.65 1
99.25 2
....

In an empty column, in same row (we'll use row 1 here for example) enter
=IF(B1=1,A1*.25,IF(B1=2,A1*.50,IF(B1=3,A1*.75,"")) )
extend that formula down the column to match entries in columns A/B.
Substitute actual colum letters for A and B in the formula.


"Dan" wrote:

Hi, I have a large series of numbers saved in one column
[34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by
[1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column.

I need to calculate percentiles (25%,50% and 75%) of these numbers for
different indexes. In another word, I need to calculate percentiles for those
numbers with index of 1, 2, ... respectively, not the percentiles of all the
numbers.

Thanks a lot!

If I've misinterpreted your problem, let me know. I'm thinking that perhaps
you want to calculate what percentage of the numbers with those indexes are
in comparison to the total number of all that are indexed?

"Dan" wrote:

Hi, I have a large series of numbers saved in one column
[34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by
[1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column.

I need to calculate percentiles (25%,50% and 75%) of these numbers for
different indexes. In another word, I need to calculate percentiles for those
numbers with index of 1, 2, ... respectively, not the percentiles of all the
numbers.

Thanks a lot!

thats how i read it <G
therefore in a cell at the bottom of your range(or at the top as long as the
whole range is selected) =countif(A1:A100,C1)/count(A1:A100) format as
percentage.
The reason i have a cell reference in the formula is so yu can type in 1 or
2 or 3 and the percentage shows,or of course have three seperate formulas
for each index
--
paul

remove nospam for email addy!



"JLatham" wrote:

If I've misinterpreted your problem, let me know. I'm thinking that perhaps
you want to calculate what percentage of the numbers with those indexes are
in comparison to the total number of all that are indexed?

"Dan" wrote:

Hi, I have a large series of numbers saved in one column
[34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by
[1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column.

I need to calculate percentiles (25%,50% and 75%) of these numbers for
different indexes. In another word, I need to calculate percentiles for those
numbers with index of 1, 2, ... respectively, not the percentiles of all the
numbers.

Thanks a lot!

Well, either way it goes, he has a solution! Good of you to take the time to
work up the formulas for him.

I read it one way, then the other way, then back ... and of course after I
posted, I thought "what if I'm wrong?"

"paul" wrote:

thats how i read it <G
therefore in a cell at the bottom of your range(or at the top as long as the
whole range is selected) =countif(A1:A100,C1)/count(A1:A100) format as
percentage.
The reason i have a cell reference in the formula is so yu can type in 1 or
2 or 3 and the percentage shows,or of course have three seperate formulas
for each index
--
paul

remove nospam for email addy!



"JLatham" wrote:

If I've misinterpreted your problem, let me know. I'm thinking that perhaps
you want to calculate what percentage of the numbers with those indexes are
in comparison to the total number of all that are indexed?

"Dan" wrote:

Hi, I have a large series of numbers saved in one column
[34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by
[1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column.

I need to calculate percentiles (25%,50% and 75%) of these numbers for
different indexes. In another word, I need to calculate percentiles for those
numbers with index of 1, 2, ... respectively, not the percentiles of all the
numbers.

Thanks a lot!

Hi JLatham,

Thanks for your kind help. But I think I did not make my question clear.
Basically I am calculating the 25% percentiles for numbers indexed "1", "2",
"3",...., "N" respectivelly. There are a lot of numbers with a big uplimit of
index "N" so that selecting numbers of each index manually is not easy.

In another word, if N = 99, then I need to calculate 25% percentiles for
those numbers indexed 1, 2, ..., 99 respectively. That is it.

Thanks agian for your help and please let me know if I am not express myself
clearly.

- Hua


"JLatham" wrote:

Well, either way it goes, he has a solution! Good of you to take the time to
work up the formulas for him.

I read it one way, then the other way, then back ... and of course after I
posted, I thought "what if I'm wrong?"

"paul" wrote:

thats how i read it <G
therefore in a cell at the bottom of your range(or at the top as long as the
whole range is selected) =countif(A1:A100,C1)/count(A1:A100) format as
percentage.
The reason i have a cell reference in the formula is so yu can type in 1 or
2 or 3 and the percentage shows,or of course have three seperate formulas
for each index
--
paul

remove nospam for email addy!



"JLatham" wrote:

If I've misinterpreted your problem, let me know. I'm thinking that perhaps
you want to calculate what percentage of the numbers with those indexes are
in comparison to the total number of all that are indexed?

"Dan" wrote:

Hi, I have a large series of numbers saved in one column
[34,5,21,98,34,3,432,55,27,90,1125,......]. These data are indexed, e.g. by
[1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,......] which are in another column.

I need to calculate percentiles (25%,50% and 75%) of these numbers for
different indexes. In another word, I need to calculate percentiles for those
numbers with index of 1, 2, ... respectively, not the percentiles of all the
numbers.

Thanks a lot!