Open In App

How to Winsorize Data in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

To winsorize information means to set outrageous exceptions equivalent to a predefined percentile of the information. For instance, a 90% winsorization sets all perceptions more noteworthy than the 95th percentile equivalent to the worth at the 95th percentile and all perceptions, not exactly the fifth percentile equivalent to the worth at the fifth percentile. This instructional exercise gives a bit-by-bit illustration of how to winsorize a dataset in Excel.

Winsorize Data in Excel

Step 1: Create the Data

To begin with, we’ll make the accompanying dataset:

Creating-dataset

 

Stage 2: Calculate the Upper and Lower Percentiles

For this model, we’ll play out a 90% winsorization. This implies we’ll set all values more prominent than the 95th percentile equivalent to the 95th percentile and all values less than the 4th percentile equal to the 4th percentile. The accompanying recipes tell the best way to track down the fourth and 95th percentiles:

Upper-and-Lower-percentiles-calculated

 

We will get the below results,

Percentile-obtained

 

The fourth percentile ends up being 14.36, and the 95th percentile ends up being 80.

Step 3: Winsorize the Data

Ultimately, we’ll utilize the accompanying recipe to winsorize the information:

Winsorizing-data

 

Note that we simply duplicate and glue the recipe in cell F2 down to the leftover cells in section F. Well, get the below result:

Winsorized-data

 

For this situation, the worth of 2 became changed to 14.36, 13 became changed to 14.36, and the worth of 98 became changed to 80. Note that in this model, we played out a 90% winsorization, yet it’s feasible to likewise play out an 80% winsorization, 95% winsorization, close to 100% winsorization, and so on by essentially working out various upper and lower percentiles.


Last Updated : 22 Aug, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads