How to Calculate the Average & Distribution of Numbers through VBA

Excel VBA has very powerful functions, which can help you solve many tiring problems. The VBA codes also make the Excel the most popular office software. And in today’s article, we’ve introduced a new function.

Some people may argue that the VBA is so difficult to master. actually, if you can understand the fundamental rules of VBA, it will also become easy to you.

An Example of Calculating Average Scores

In this image, you can see that there are scores of three classes. And there are many students in each class.

And now, you will need to calculate the average scores of each class. Furthermore, you also want to know the distribution of student scores, so you can adjust your teaching plan accordingly.

One way to finish this task is to calculate those numbers one by one. However, you will find that the efficiency of this way is really low. Therefore, you can use VBA code to meet your need.

VBA Code of to Achieve the Purpose

1. Press the key “Alt + F11” on the keyboard. And then a new window will show up. And in this interface, you can perform some VBA operations.
2. Double click the “sheet1” on the left of the VBA interface. After this step, a new window will pop up.
3. Then input the following VBA code into this window.

Function CalculateAverage(ByVal strSheetName As String, ByVal nScoreCol As Integer) As Double

Dim rwIndex As Integer,dSum As Double

dSum = 0

For rwIndex = 2 To Worksheets(strSheetName).UsedRange.Rows.Count

    dSum = dSum + Worksheets(strSheetName).Cells(rwIndex, nScoreCol).Value

Next rwIndex

       CalculateAverage=dSum/(Worksheets(strSheetName).UsedRange.Rows.Count-2+1)

End Function

This is the function of calculating average scores. And the following is to count the distribution.

Function CalculateCountInRange(ByVal strSheetName As String, ByVal nScoreCol As Integer, ByVal nLowerLimit As Integer, ByVal nUpperLimit As Integer) As Integer

Dim rwIndex As Integer, nScore As Integer

CalculateCountInRange = 0

For rwIndex = 2 To Worksheets(strSheetName).UsedRange.Rows.Count

    nScore = Worksheets(strSheetName).Cells(rwIndex, nScoreCol).Value

    If (nScore >= nLowerLimit) And (nScore <= nUpperLimit) Then

        CalculateCountInRange = CalculateCountInRange + 1

    End If

Next rwIndex

End Function

VBA Code of Student Distributions

1. First, follow the first two steps in the previous part.
2. And then input the following VBA code.

Sub CalulateStatistics()

Dim rwIndex As Integer, colIndex As Integer, nLowerThanAverage As Integer, dSum As Double, dAverage As Double, nUpperLimit As Integer, nCount As Integer

dAverage = CalculateAverage(“Class 1”, 3)

nUpperLimit = Int(dAverage)

If (nUpperLimit = dAverage) Then

    nUpperLimit = nUpperLimit – 1

End If

nCount = CalculateCountInRange(“Class 1”, 3, 0, nUpperLimit)

Worksheets(“Statistics”).Cells(3, 2) = dAverage

Worksheets(“Statistics”).Cells(3, 3) = nCount

Worksheets(“Statistics”).Cells(3,4)=CalculateCountInRange(“Class1”,3,0,59)

Worksheets(“Statistics”).Cells(3,5)=CalculateCountInRange(“Class1”,3,60,69)

End Sub

3. Run the macro and then you can see the result in the sheet of “statistics”.

Change the upper limit and the lower limit of scores and then you can know the numbers of each stage. For other classes, you can simply change the class number in the code. What’s more, in your real work, you need to change some parameters in the code.

How to Handle Excel File corruption

In using Excel, you may suffer from the following problems: you couldn’t open the file; some data lost in your file; you couldn’t find the file and so on. And those problems are all called Excel disasters. So we recommend that you prepare the repair software to recover Excel files. In Addition, the tool can be very useful and helpful in dealing with data disasters.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit www.datanumen.com