Using the averaging approach and descriptive statistics can anyone tell me how do i find outliers? i know how to do it using the 5-number summary but cant figure out how to do it this way
thanks.
Hello,
When evaluating a set of items for their popularity, I pull popularity metrics from two different sources, aggregating each score into an average. While I am satisfied with this metric (Let's call it the 'popularity score'), I'd like to create a simplified, more meaningful score.
My initial though was to create an index using the following formula:
Code:
(D3/MAX($D:$D))*100)
Where D3 is the individual popularity score and D represents all popularity scores.
I discovered, however, that outliers would have a disruptive effect on the data. For instance, if there where several VERY popular items, other items with acceptable popularity scores would appear very low in relative terms.
Any ideas on how to account for the Outliers while still retaining the meaning in an easy to understand way?
Here is part of the data:
Here is my data (D3:D29):
0.00
0.00
0.00
42.56
0.00
680.99
0.00
0.00
60.80
0.00
18.24
0.00
85.12
170.24
0.00
0.00
297.92
103.36
0.00
0.00
0.00
30.40
18.24
30.40
72.96
0.00
A colleague suggest that I take the lognormal distribution and then take the antilog. Any idea how to implement, or if this is a sound approach?
Hi everyone...
here is the situation...i need a formula for median to exclude the min and the max value ..that are above 0!!!
i tried this formula ...(ARRAY!) but i get Num error! ..any ideas?
suppose H28:H32 is the data range!
{=+MEDIAN(IF(H28:H32>0,IF(H28:H32>MIN(H28:H32),IF(H28:H32<MAX(H28:H32),H28:H32))))}
Hi,
I have created a graph from pivot table (see example attached), I would like to get a legend beside it with statistics data such as mean std dev etc.
Can it be done in excel ?
Thanks
Hi...any chance of this working?...
i have a graph showing a count of patients by how many weeks they have waited
But i need to show what the median wait would be...
I have all this in figures but is it possible to show the median as one of the bars.
i.e. all the bars from weeks 01 - 40 weeks are blue, but the 20th week is showing red to highlight the median?? is this dooable?
Thanks v much
Liz x
I'm really wrestling with this one. How do I plot 3 median scores taken in Fall, Winter, and Spring (ie. on 9/1/08, take median of B15:B17 and have that appear on 9/1/08 on chart. On 1/5/09, take median of B18:B20 and have that appear at 1/5/09 on chart. On 5/1/09, take median of B21:B23 and have that appear at 5/1/09 on chart. Column A has the dates, so for example, A15 through A17 are all dated 9/1/08, A18:A20 are all dated 1/5/09, A21:A23 are dated 5/1/09). I'd like a nice distribution of those 3 points. Help.
Is there a way for excel to automatically determine outliers in a given data
set and exclude them from future calculations?
I have lots of data, mostly triplicate results, with some outliers. I would
appreciate help on applying Thompson tau or other outlier removal technique
in Excel.
Thanks!
Is there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.
The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier.
A user defined function is OK with me, if it is impossible to use the built-in functions. Any suggestion?
Code:
1
2
3
4
5
6
7
1000
8
9
10
11
12
Morning All,
Does anyone know of a free tutorial that can explain how to get statistical data in Excel? - I have seen it done before (in excel) for 'Linear regression', I am wondering if the same can be done for "Polynomal"?
Rgs
Neville
