CODEWITHSUNDEEP
excelvba
Robert Kendall
Robert Kendall

Reputation: 390

Find the average of values in an array (Excel VBA)

How do I find the average of the values held in a 2D array in excel VBA? I have an array named "Numbers" which has values of 1,2,3,4,6 How do I enter the average value into a value named NumbersAve

Upvotes: 0

Views: 4628

Answers (1)

user4039065
user4039065

Reputation:

Application.Average is sufficient.

Dim numbers As Variant, NumbersAve As Double

'by rows
Range("A1") = 1
Range("A2") = 2
Range("A3") = 3
Range("A4") = 4
Range("A5") = 6

numbers = Range("A1:A5").Value2
Debug.Print LBound(numbers, 1) & " to " & UBound(numbers, 1) & ", " & _
            LBound(numbers, 2) & " to " & UBound(numbers, 2)  '<~~ 1 to 5, 1 to 1

NumbersAve = Application.Average(numbers)
Debug.Print NumbersAve                   '<~~ 3.2

'by columns
Range("A1") = 1
Range("B1") = 2
Range("C1") = 3
Range("D1") = 4
Range("E1") = 6

numbers = Range("A1:E1").Value2
Debug.Print LBound(numbers, 1) & " to " & UBound(numbers, 1) & ", " & _
            LBound(numbers, 2) & " to " & UBound(numbers, 2)  '<~~ 1 to 1, 1 to 5

NumbersAve = Application.Average(numbers)
Debug.Print NumbersAve                   '<~~ 3.2

Upvotes: 2

Related Questions