How to Perform Bootstrapping in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset bootstrapping in excel

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.

Sample Preparation

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.

Mean bootstrapping in excel

  • Similarly, AutoFill downward till sampling 30.

Sample

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.

Mean of means bootstrapping in excel

  • After that, AutoFill the formula up to N34.

mean

  • 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.

Cut-Off bootstrapping in excel

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.

Cut-Off

  • 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.

Lower Bound

  • 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.

bootstrapping in excel


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo