Finding and Segregating Outliers

Hi all,

I am a scientist using Excel for my data analysis needs. I have my data arranged in a matrix where each column is a different timepoint an each row is a different subject in my experiment. I need a program to do the following:

- For each column, identify every value in that column which is above/below a given threshold.
- Take the row that contains the aforementioned value and move it to a pre-determined location.

I imagine Excel can do this fairly easily, but I'm not sure how. Many thanks in advance.

- StatsFan

Hi

How do you want to identify the threshold? Where do you want that data to go? How about an example file that shows your structure (sheet names, headings etc) and give some examples.

rylo

Thanks for your response Rylo

The threshold is a pre-determined value that I have settled on (0.15). I'd like the data to go somewhere else on the spread sheet; I still want to use it but just not have it 'contaminate' my main data matrix. I've attached my analysis template. The area on the left is for raw data and the corresponding area on the right is normalized data. Thanks.

Hi

Can you update your example file to give us a before and after view?

rylo

OK, I attached an example.

In the "before", there is all my raw data on one side, and the normalized data on the other. Stats for each found below each.

In the after, same deal except now the outliers from raw data have been moved to an empty section below the stats. Here's the tricky part,I am only scanning for outliers in columns H,M N,S and T,Y (these are the times = 0 and 45 min for each of three groups of data). I have been using COUNTIF to find how many values are <0.15, then manually removing them and placing them in some empty spot. Also, I then delete the corresponding rows in the normalized section because they still reference the data I removed.

Hope this makes (some) sense. Thanks.

- StatsFan

Hi

Before you start, make the formulas in G:G202 into values.

I've used Sheet3 as the output sheet. Select the sheet BEFORE and run the following

Please Login or Register  to view this content.

See how that goes.

rylo

Thanks so much! I will play around with this and let you know how it goes.