Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. This article will show you how to perform bootstrapping in Excel with easy steps.
Introduction to Bootstrapping
Bootstrapping is a method of constructing a confidence interval for a statistic. It follows some characteristics.
- The sample size is small.
- The distribution is unknown.
How to Perform Bootstrapping in Excel: with Easy Steps
This is the dataset for today’s article. I have an original dataset and have to prepare some samples from this data.
I will use this dataset and samples to perform bootstrapping.
Step 1: Prepare Samples
The first step is to prepare bootstrapping samples from the original dataset. To do so, I will use a combination of the INDEX, ROWS, and RAND functions.
- Go to D5 and write down the following formula
=INDEX($B$5:$B$34,ROWS($B$5:$B$34)*RAND()+1)- Then, press ENTER to get the output.
Formula Breakdown
- RAND() → Returns a random number greater than 0 and lesser than 1.
- Output: The output will vary every time you refresh the file.
- ROWS($B$5:$B$34) → Returns the number of rows in the given range.
- Output: 30
- INDEX($B$5:$B$34,ROWS($B$5:$B$34)*RAND()+1) → Returns you the result
- Output: The output will vary every time you refresh the file.
- After that, use the Fill Handle to AutoFill up to M5.
- Similarly, AutoFill downward till sampling 30.
Read More: How to Calculate Bootstrapping Spot Rates in Excel
Step 2: Calculate Mean of Means
Now, I will calculate the mean of all the means of these 30 samples. But before that, I have to calculate the mean of each sample. The function that is going to be handy is the AVERAGE function.
- To do so, go to N5 and write down the following formula
=AVERAGE(D5:M5)- Then, press ENTER to get the output.
- After that, AutoFill the formula up to N34.
- Now, I will calculate the mean of means. To do so, write down the following formula in Q4.
=AVERAGE(N5:N34)- Then, press ENTER to get the output.
Read More: How to Find Mean, Median, and Mode on Excel
Step 3: Measure Confidence Interval
In the following step, we will measure the confidence interval. The function to be used is the PERCENTILE.EXC function. But before that, I will require a cut-off value.
- To calculate the cut-off value, go to Q5 and write down the following formula
=Q4*0.05- Then, press ENTER to get the output.
- I have assumed a 90% confidence interval. That’s why I have multiplied the mean of means with 05.
- Then, I will calculate the lower bound. For this, go to Q6 and write down the following formula
=PERCENTILE.EXC(D5:M34,0.05)- Then, press ENTER to get the output.
- In a similar way, I will calculate the upper bound. The formula will become
=PERCENTILE.EXC(D5:M34,0.95)- Then, press ENTER to get the output.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In this article, I have demonstrated how to perform bootstrapping in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.
