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

Question

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!

Thank you, this answered my question perfectly! – matyd Mar 23 '18 at 00:35 — matyd, Mar 23 '18 at 00:35

score 2 · Accepted Answer · answered Mar 23 '18 at 01:17

2

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

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

answered Mar 23 '18 at 01:17

score 0 · Answer 2 · edited Mar 23 '18 at 07:24

0

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

edited Mar 23 '18 at 07:24

Pᴇʜ

56,719
10
49
73

answered Mar 23 '18 at 02:03

gina1751

1

Don't use `Integer` for row counts, Excel has more rows than `Integer` can handle! I recommend always use `Long` instead of `Integer` because there is [no benefit in using `Integer` at all](https://stackoverflow.com/a/26409520/3219613). – Pᴇʜ Mar 23 '18 at 07:23

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

2 Answers2