How to Calculate Intraclass Correlation Coefficient in Excel

 

What Is the Intraclass Correlation Coefficient?

The Intraclass Correlation Coefficient (ICC) determines the reliability of ratings for multiple items. The ratings are measured by comparing the variability of different ratings of the same item (or subject) rated reliably by different users.
The ratings are quantitative. The value of an ICC can range from 0 to 1, where 0 means no reliability and 1 means perfect reliability among raters.

The calculation takes into account the following:

Factors Description
Model
  • One-Way Random Effects: This model indicates that each item is rated by a different group of randomly chosen raters. The raters are considered the source of random effects while using this model. As the same group of raters usually rate each item, so this model is rarely used in practice.
  • Two-Way Random Effects: This model indicates that each item is rated by a number of randomly selected raters from a population. Both the raters and the subjects are considered the sources of random effects while using this model. When we want to generalize the findings to any raters similar to the raters used in the study, then this model is used.
  • Two-Way Mixed Effects: This model indicates that each item is rated by a number of randomly selected raters from a population. The chosen group of raters are the only raters of interest for this model. So, we don’t want to generalize the findings to any other raters who share similar characteristics as the raters used in the study.
Type of Relationship
  • Consistency: The systematic differences between the ratings of judges.
  • Absolute Agreement: The absolute differences between the ratings of judges.
Unit
  • Single Rater: When only a single rater is the base for the measurement.
  • The Mean of Raters: When the mean of ratings from all the judges is the base for the measurement.

How to Calculate the Intraclass Correlation Coefficient (ICC) with Example

We have four different judges who rated the quality of 10 different products. The ratings are shown below.

Steps:

  • Go to the Data tab and select Data Analysis.

Selecting Data Analysis Tool to How to Calculate Intraclass Correlation Coefficient in Excel

  • A Data Analysis pop-up window will appear. Select Anova: Two-Factor Without Replication.
  • Click OK.

  • The Anova: Two-Factor Without Replication pop-up window will appear. In the Input Range box, drag through the whole range from your dataset that contains the rating values. For our sample, the range is C5:F14.
  • In the Output Range box, click on the cell in your dataset that you want as the starting cell of the output result. We selected cell H4.
  • Click OK.

Selecting Range for How to Calculate Intraclass Correlation Coefficient in Excel

  • You will get the outcome of the Anova: Two-Factor Without Replication in your worksheet.
  • You will be able to see the whole Summary of the calculations along with the individual Count, Sum, Average, and Variance of the ratings by the Judges for the Products.

Summary of How to Calculate Intraclass Correlation Coefficient in Excel

  • To measure the ICC based on those rating variations, you need the bottom Anova table for input values.
  • The formula to calculate the Intraclass Correlation Coefficient (ICC) for the extracted output is:
=(K26-K28)/(K26+J27*K28+(J27+1)*(K27-K28)/(J26+1))

Result of How to Calculate Intraclass Correlation Coefficient in Excel

  • The Intraclass Correlation Coefficient (ICC) for our dataset is 0.08840678.

The interpretation of the value of an Intraclass Correlation Coefficient (ICC) is as follows:

  • Less than 0.50 -> Poor reliability
  • Between 0.5 and 0.75 -> Moderate reliability
  • Between 0.75 and 0.9 -> Good reliability
  • Greater than 0.9 -> Excellent reliability

Read More: How to Calculate Spearman Correlation in Excel


Download the Workbook


Related Articles


<< Go Back to Correlation Coefficient in Excel | Excel Correlation | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo