calculate percentile for indexed numbers. Thanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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?
 
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
(e-mail address removed)
remove nospam for email addy!



JLatham said:
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 said:
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!
Click to expand...
Click to expand...
 
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 said:
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
(e-mail address removed)
remove nospam for email addy!



JLatham said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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
(e-mail address removed)
remove nospam for email addy!



JLatham said:
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?

:

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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top