How to Calculate Pooled Variance in Excel (with Easy Steps)

In this article, we will show how to calculate pooled variance in Excel with easy steps using vivid illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What Is Pooled Variance?

Pooled Variance is a statistical term also known as combined variance or composite variance. It indicates the average variance of two or groups. It is the single common variance among the groups. Mathematically Pooled Variance can be shown as:

Where,

n= Sample size of Group 1,

n2 = Sample size of Group 2,

S12 = Variance of Group 1,

S22 = Variance of Group 2,

Sp2 = Pooled Variance

When the sample sizes are the same (n1=n2), then we can use this simplified formula.


Steps to Calculate Pooled Variance in Excel

Step 1: Input Data and Form a Table

In this step, we will collect sample data to make a dataset and form a Table. This Table will make our calculation easy.

Pooled Variance Calculation: Sample Dataset

  • Insert sample data from two different sources into two columns Group 1 and Group 2 in Excel.
  • Now, we will form two tables. Select the cells of the Group 1 column.
  • Then choose Table from the Insert tab.

Create Dataset and Table in Excel for Variance

  • Now, Create Table window appears.
  • Our selected range is shown here. Check My table has headers option.
  • Finally, press OK.

Create Dataset and Table in Excel for Variance

  • Now, click on the Table Design tab.
  • Uncheck Filter Button and Banded Rows from Table Style Options.
  • After that, set the name of the table in the Table Name section.

Create Dataset and Table in Excel for Variance

  • Similarly, create the other table named Group2. Have a look at the dataset. The little arrow in the bottom-right corner of the table denotes that, it’s a ‘table’. You can modify a table in size with this.

Read More: How to Do Variance Analysis in Excel (With Quick Steps)


Step 2: Count the Number of Samples

The COUNT function counts the number of cells in a range that contains numbers.

In this step, we will determine the size of the sample data using the COUNT function.

  • First, add a row to determine the sample size.

  • Go to Cell C16 of the Group 1 column. Put the following formula on that cell.
=COUNT(Group1)

Calculate Data Size of Excel Column

  • Then, press the Enter button.

We get the data size of Group1.

  • Put the similar formula referring to the Group2 table.

Calculate Data Size of Excel Column

Read More: How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)


Similar Readings


Step 3: Calculate Variance with Excel VAR.S Function

The VAR.S function estimates variance based on a sample (ignores logical values and text in the sample).

In this step, we will determine the variance. Excel has a default function for this.

  • We add a new row to calculate variance in the dataset.

  • Go to Cell C17 and put the following formula.
=VAR.S(Group1)

Calculate Variance from Sample in Excel

  • Press the Enter button to get the result.

We get the variance for the data of the Group 1 column.

  • Create a similar formula for Group 2 and put that on Cell D17 and press Enter to get the result.

Calculate Variance from Sample in Excel

As we are using Table as the reference Fill Handle feature does not work here.

Read More: How to Calculate Sample Variance in Excel (2 Effective Approaches)


Step 4: Determine Pooled Variance with Formula

Finally, we will calculate the Pooled Variance. We will apply a mathematical formula.

  • Add a row for the Pooled Variance.

  • Put the formula on Cell C18.
=((C16-1)*C17+(D16-1)*D17)/(C16+D16-2)

Determine Pooled Variance

  • Press the Enter button to get the result.

As we previously mentioned, when the sample size is the same as that time, we can use a simplified formula.

  • Now, apply that simplified formula to Cell C19.
=(C17+D17)/2

Determine Pooled Variance

  • Again, press the Enter button to get the result.

Read More: Calculate Percentage Variance between Two Numbers in Excel


Conclusion

In this article, we described each step to calculate the pooled variance in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo