Introduction to Bootstrapping
Bootstrapping is a method of constructing a confidence interval for a statistic with the following features:
- The sample size is small.
- The distribution is unknown.
How to Perform Bootstrapping in Excel: with Easy Steps
We have an original dataset and have to prepare some samples from this data.
Step 1 – Prepare the Samples
- Go to D5 and insert the following formula
=INDEX($B$5:$B$34,ROWS($B$5:$B$34)*RAND()+1)- 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.
- Use the Fill Handle to AutoFill up to M5.
- AutoFill down to sample 30.
Step 2 – Calculate the Mean of Means
- Go to N5 and use the following formula
=AVERAGE(D5:M5)- Press Enter to get the output.
- AutoFill the formula up to N34.
- We will calculate the mean of means with the following formula in Q4.
=AVERAGE(N5:N34)- Press Enter to get the output.
Read More: How to Find Mean, Median, and Mode on Excel
Step 3 – Measure the Confidence Interval
- To calculate the cut-off value, go to Q5 and use the following formula
=Q4*0.05- Hit Enter.
- We have assumed a 90% confidence interval. That’s why we have multiplied the mean of means with 0.05.
- We will calculate the lower bound in Q6 with the following formula
=PERCENTILE.EXC(D5:M34,0.05)- Hit Enter.
- We will calculate the upper bound in Q7:
=PERCENTILE.EXC(D5:M34,0.95)- Hit Enter.
Download the Practice Workbook
Related Articles
- Comparison Among MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL Functions in Excel
- How to Resample Time Series in Excel
- How to Calculate Margin of Error in Excel
- How to Calculate Bootstrapping Spot Rates in Excel
<< Go Back to Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
