how do i winsorize data in excel
 

I have some outliers in a set of data, and would like another method, besides
the TRIMMEAN to smooth the outliers. Trimmean "eliminates" the outliers...
how do I Winsorize a set of data, in order to smooth the values?

Answer: how do i winsorize data in excel
 

Winsorizing is a great way to deal with outliers in a dataset. Instead of eliminating them like TRIMMEAN, Winsorizing replaces the outliers with the nearest non-outlier value. Here's how you can Winsorize data in Excel:

1. First, sort your data in ascending or descending order.
2. Next, determine the percentage of data you want to Winsorize. For example, if you want to Winsorize the top and bottom 5% of your data, you would set your percentage to 5%.
3. Calculate the number of data points you need to Winsorize by multiplying the percentage by the total number of data points. For example, if you have 100 data points and want to Winsorize the top and bottom 5%, you would multiply 100 by 0.05 to get 5 data points.
4. Replace the outliers with the nearest non-outlier value. For example, if you're Winsorizing the top 5% of your data, you would replace the top 5 data points with the 6th highest value in your dataset. If you're Winsorizing the bottom 5% of your data, you would replace the bottom 5 data points with the 6th lowest value in your dataset.
5. Once you've replaced the outliers, you can calculate the mean or median of your Winsorized dataset.

To automate this process, you can use the following formula in Excel:

Replace "data" with the range of cells containing your dataset, and "percentage" with the percentage of data you want to Winsorize. For example, if your data is in cells A1:A100 and you want to Winsorize the top and bottom 5%, you would use the formula:

This will return a new range of cells with the Winsorized data.

how do i winsorize data in excel
 

I'm not familiar with outliers and found this:

There is no rigid mathematical definition of what constitutes an outlier;
determining whether or not an observation is an outlier is ultimately a
subjective exercise.
Some methods which are commonly used for identification assume that the data
are from a normal distribution, and identify observations which are deemed
"unlikely" based on mean and standard deviation: (from Wikipedia)

With that said

Here is some syntax for Winsorizing:
http://www.itl.nist.gov/div898/softw...lar/winsor.htm

Good luck!

--
Thank you,

scrowley(AT)littleonline.com


"Samira Pimentel" wrote:

I have some outliers in a set of data, and would like another method, besides
the TRIMMEAN to smooth the outliers. Trimmean "eliminates" the outliers...
how do I Winsorize a set of data, in order to smooth the values?