how to use worksheetfunction.averageif, with multiple variables

[dka90]

Hi All,

I have 5 different values in five variables, in that some values are negative, so i want to take average only for positive values..


plz help me


thanks
Deepak

 

[Learning To Excel]

Without placing the values into a range I'm not sure that you can use the averageif function. You could put the variables into an array, use an if statement to add the positive values, count each time a new positive is added, then divide the total by the count. Here is an example:

Sub test()
Dim x As Double
Dim y As Double
Dim z As Double
Dim a As Double
Dim b As Double
Dim c As Double
Dim Answer As Double
Dim Holder(5) As Variant
Dim Count As Integer
Dim i As Integer


x = 5
y = -2
z = 500
a = 6
b = -40
c = 3


Holder(0) = x
Holder(1) = y
Holder(2) = z
Holder(3) = a
Holder(4) = b
Holder(5) = c


    For i = LBound(Holder) To UBound(Holder)
        If Holder(i) > 0 Then
            Answer = Answer + Holder(i)
            Count = Count + 1
        End If
    Next i
    
Answer = Answer / Count


Debug.Print Answer


End Sub

Here is an example of putting the variables to a range, using the function, then clearing the range.

Sub test()
Dim x As Double
Dim y As Double
Dim z As Double
Dim a As Double
Dim b As Double
Dim c As Double
Dim Answer As Double


x = 5
y = -2
z = 500
a = 6
b = -40
c = 3


Range("A1").Value = x
Range("A2").Value = y
Range("A3").Value = z
Range("A4").Value = a
Range("A5").Value = b
Range("A6").Value = c


Answer = Application.WorksheetFunction.AverageIf(Range("A1:A6"), ">" & 0)


Range("A1:A6").Clear


Debug.Print Answer


End Sub

 

[dka90]

Hi,

Thank you so much for your help....
its working good

Thanks,
Deepak