A VBA function for Cohen’s effect size

While many social scientists tend to get excited to report p-values of less than 0.05 when comparing two groups, they tend to forget to report effect size. One can get a low p-value just by having a very large sample size but it is the effect size that tells you how much pragmatic difference there is between two groups. Although there are simple formulas out there for effect size, it is better to have a formula for two groups of unequal size. I wrote a VBA program for the Cohen’s effect size and you can use it readily.  You can download it form here.  If you are not familiar with how to enter the VBA program, here is a quick tutorial.  You just have to save the excel file as a .xlsm file.

Example of usage is
where A23:A78 has the control group numbers and B24:B67 has the experimental group numbers.

Function EffectSizeCohen(ControlGroup As Range, ExperimentalGroup As Range) As Variant
'This function finds the Cohen's effect size given the numbers from the control
'and experimental group
'ControlGroup: Excel range of numbers for control group, e.g. A120:230
'ExperimentalGroup: Excel range of numbers for experimental group, e.g. A120:230
'Cohen's effect size.
'See formula at

'Putting the ranges in a column vector. Note that excel stores it as as two dimensional matrix though
ControlData = ControlGroup.Value
ExperimentalData = ExperimentalGroup.Value

'Number of entries of the two groups
ControlLength = ControlGroup.Count
ExperimentalLength = ExperimentalGroup.Count

'Calculating the average and standard Deviation of control group
ControlSum = 0
For i = 1 To ControlLength
ControlSum = ControlSum + ControlData(i, 1)
Next i
ControlAvg = ControlSum / ControlLength

ControlVar = 0
For i = 1 To ControlLength
ControlVar = ControlVar + (ControlData(i, 1) - ControlAvg) ^ 2
Next i
ControlStd = (ControlVar / (ControlLength - 1)) ^ 0.5

'Calculating the average and standard Deviation of experimental group
For i = 1 To ExperimentalLength
ExperimentalSum = ExperimentalSum + ExperimentalData(i, 1)
Next i
ExperimentalAvg = ExperimentalSum / ExperimentalLength
ExperimentalVar = 0
For i = 1 To ExperimentalLength
ExperimentalVar = ExperimentalVar + (ExperimentalData(i, 1) - ExperimentalAvg) ^ 2
Next i
ExperimentalStd = (ExperimentalVar / (ExperimentalLength - 1)) ^ 0.5

'Calculating the Cohen's effect size
'See formula at https://www.statisticssolutions.com/free-resources/directory-of-statistical-analyses/effect-size/
StdMean = (((ControlLength - 1) * (ControlStd) ^ 2 + (ExperimentalLength - 1) * (ExperimentalStd) ^ 2) / (ControlLength + ExperimentalLength)) ^ 0.5
EffectSizeCohen = (ExperimentalAvg - ControlAvg) / StdMean
End Function

Leave a Reply