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:
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:
We will get the below results,
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:
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:
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.