Formula Help

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

Guest

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

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(Data>A1,Data))

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




--ron
 
Just figured out how to do the Median using an array formula -
{=MEDIAN(IF(AM8:AM12>AM24,AM8:AM12))} but can't figure out how to do
percentile using an array formula, can anyone help?
Cheers
 
Thanks Ron, much help!!!
When i was trying to do a percentile array, i missed the comma after the )
Thanks!!!!
Rehanna
 
Thanks Ron, much help!!!
When i was trying to do a percentile array, i missed the comma after the )
Thanks!!!!
Rehanna
Click to expand...

You're welcome. Thanks for the feedback.


--ron
 
Back
Top