In this tutorial you will learn How to Write AVERAGEIF and AVERAGEIFS Functions in VBA
How to Write AVERAGEIF and AVERAGEIFS Functions in VBA
To Write AVERAGEIF and AVERAGEIFS Functions in VBA you can use the following ways:
Approach 1 :AVERAGEIF Function in VBA
Sub Averageif_Function()
Range("E2") = WorksheetFunction.AverageIf(Range("A2:A10"), "Newcastle United", Range("B2:B10"))
End Sub
This specific example will calculate the average value in the range B2:B10 where the matching value in the range A2:A10 equals “New Castle United” and then assign the result to cell E2.
Example : AVERAGEIF Function in VBA
The below example which consist of 10 football teams shows how to use the above VBA code in practice.
When we run the above provided Macro we get the following result.
You’ll see that the value 25 is present in cell E2.
This indicates that the New Castle players average 5 goals per game, according to the points column.
By carefully computing the average Goals scored value for the New Castle Players, we can confirm that this is true:
Average goals scored for New Castle Players: (45 + 30 + 20 + 5) / 4 = 25.
Approach 2: AVERAGEIFS Function in VBA
Consider a scenario in which you want to determine the average value in the Goal Scored Column for the players who fit the following description:
- Player plays for the New Castle United .
- Over 5 Goals were scored by the player.
We can run the below macro to use the AVERAGEIFS function:
Sub Averageifs_Function()
Range("E2") = WorksheetFunction.AverageIfs(Range("C2:C10"), Range("A2:A10"), "Newcastle United", Range("B2:B10"), ">30")
End Sub
When we run the above provided Macro we get the following result.
How to Write AVERAGEIF and AVERAGEIFS Functions in VBA
In conclusion, writing AVERAGEIF and AVERAGEIFS functions in VBA can be a game-changer for your data analysis needs. By using these functions, you can easily calculate averages based on specific criteria and make better-informed decisions.
Remember to use the correct syntax when writing these functions and always test them thoroughly before implementing them into your code. With practice, you can become proficient in using AVERAGEIF and AVERAGEIFS functions, saving time and increasing efficiency in your programming tasks. So go ahead, give it a try! Happy coding!
Related articles :
How to Calculate Average Value of Range in VBA(Quick & Easy Guide) – 2023 (addnewskills.com)
