CODEWITHSUNDEEP
vbaexcelif-statementexcel-formulaaverage
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Average with If condition

I want to calculate the average of the cells in column E, only for the rows that have 0 in column I. It needs an if condition and then perform the standard average line of code.. I am providing the code I have written to calculate the average for all cells in column E. This code needs editing to include this if condtion. If someone knows what to add to have this if condition I would appreciate it !

Also, I am providing a screenshot

enter image description here 

lastrow = Cells(Rows.Count, "D").End(xlUp).Row
Range("C1").Formula = "=AVERAGE(E2:E" & lastrow & ")"

Upvotes: 0

Views: 4783

Answers (2)

Jarom
Jarom

Reputation: 1077

As far as I know you cannot do this with an excel function unless you make it an array function. Array functions are powerful but can be very slow at calculating. Here is a VBA solution using a VBA collection.

The answer you selected is definitely a more efficient way of getting the answer. But this code may be useful if you are wanting to manipulate those numbers in other ways since it puts them into a collection.

I made a VBA collection and added to it all values in E that corresponded to 0 values in D. Then I summed it into a variable called f and then divided it by the count of the collection. Then dropped it in the range you wanted.

Sub test()
Dim a As Collection
Dim lastrow As Integer

Set a = New Collection

lastrow = Cells(Rows.Count, "D").End(xlUp).Row

For x = 1 To lastrow
If Cells(x, 9) = 0 Then
y = Cells(x, 5).Value
a.Add (y)
End If
Next x



For Z = 1 To a.Count
f = a.Item(Z) + f
Next Z

Range("C1").Value = (f / a.Count)


End Sub

Upvotes: 2

barvobot
barvobot

Reputation: 897

formula:

=AVERAGEIF(I:I,0,E:E)

or in vba:

WorksheetFunction.AverageIf(Range("I:I"), 0, Range("E:E"))

Upvotes: 4

Related Questions