Formula Help
 

I need a formula (or other solution) that will pick out certain data in a
data set that is a particular value, and then calculate the median, 90th
and 10th percentile of this data. the only way i have been able to do it so
far is physically seperate the data, however this is tedious and
time-wasting, any tips/suggestions would be appreciated.
Cheers
e.g
Data Set
5
1
9
..05
15
And i only want to process data 4

On Tue, 11 Oct 2005 19:52:02 -0700, "Rehanna"
wrote:

I need a formula (or other solution) that will pick out certain data in a
data set that is a particular value, and then calculate the median, 90th
and 10th percentile of this data. the only way i have been able to do it so
far is physically seperate the data, however this is tedious and
time-wasting, any tips/suggestions would be appreciated.
Cheers
e.g
Data Set
5
1
9
.05
15
And i only want to process data 4

Try these **array** formulas.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place brackets {...} around the formulas.

Data is the range where your data exists. A1 is the particular value you want
to test against.

Median: =MEDIAN(IF(DataA1,Data))

10th Percentile =PERCENTILE(IF(DataA1,Data),10%)
90th Percentile =PERCENTILE(IF(DataA1,Data),90%)




--ron

Just figured out how to do the Median using an array formula -
{=MEDIAN(IF(AM8:AM12AM24,AM8:AM12))} but can't figure out how to do
percentile using an array formula, can anyone help?
Cheers

"Rehanna" wrote:

I need a formula (or other solution) that will pick out certain data in a
data set that is a particular value, and then calculate the median, 90th
and 10th percentile of this data. the only way i have been able to do it so
far is physically seperate the data, however this is tedious and
time-wasting, any tips/suggestions would be appreciated.
Cheers
e.g
Data Set
5
1
9
.05
15
And i only want to process data 4

Thanks Ron, much help!!!
When i was trying to do a percentile array, i missed the comma after the )
Thanks!!!!
Rehanna

"Ron Rosenfeld" wrote:

On Tue, 11 Oct 2005 19:52:02 -0700, "Rehanna"
wrote:

I need a formula (or other solution) that will pick out certain data in a
data set that is a particular value, and then calculate the median, 90th
and 10th percentile of this data. the only way i have been able to do it so
far is physically seperate the data, however this is tedious and
time-wasting, any tips/suggestions would be appreciated.
Cheers
e.g
Data Set
5
1
9
.05
15
And i only want to process data 4

Try these **array** formulas.

To enter an **array** formula, hold down <ctrl<shift while hitting <enter.
Excel will place brackets {...} around the formulas.

Data is the range where your data exists. A1 is the particular value you want
to test against.

Median: =MEDIAN(IF(DataA1,Data))

10th Percentile =PERCENTILE(IF(DataA1,Data),10%)
90th Percentile =PERCENTILE(IF(DataA1,Data),90%)




--ron

On Tue, 11 Oct 2005 20:41:02 -0700, "Rehanna"
wrote:

Thanks Ron, much help!!!
When i was trying to do a percentile array, i missed the comma after the )
Thanks!!!!
Rehanna

You're welcome. Thanks for the feedback.


--ron