CODEWITHSUNDEEP
vbaexcelloops
matyd
matyd

Reputation: 3

adding a for loop or if statement to excel for averaging numbers

So I have a template that I am inputting data from a csv into an excel file. The data doesn't always "file the template" so to speak or there are some fields that have '0' in them. What I am trying to do is add a for loop or something of the sort to basically 'throw out' the 0's but still average the cells say BX2-BX50. I would like excel to ignore the 0's when averaging the numbers within those fields.

Any help would be greatly appreciated!

Upvotes: 0

Views: 80

Answers (2)

gina1751
gina1751

Reputation: 1

all, so you mean the line with "0" is not the data, is only the error line when you convert it from CSV to Excel? If so, it is better to delete those lines as you know if the 0 shows as the correct data, it should reduce the overall average. you can eliminate the error data but you cannot ignore the real to lead to wrong result.

and for delete error zero line, you can use loop:

Sub removeZero()
    Dim a As Integer
    a = ActiveSheet.UsedRange.Rows.Count

    For i = 1 To a
        If Range("B" & i) = 0 Then
            Range("B" & i) = ""
        End If
    Next i

    Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

No need to over-think this. The AVERAGEIFS function should be more than sufficient.

=averageifs(BX:BX, BX:BX, "<>0")

Upvotes: 2

Related Questions