CODEWITHSUNDEEP
excelvbaaverage
user1335133
user1335133

Reputation:

Excel macro to replace missing data with average

I have a range of data, from a vba prompt held in a type range variable. I am trying to then go through this range, and for each empty cell, replace it with the average of it's column.

I can replace the empty cells fine, but it's getting the average of each column I am strugging with. Is there an easy way?

I prefer not to do a loop for each column, a loop for each row and then calculate it using total/numberOfNonEmptyCells as this takes a fair bit of time ( And i'm evidently doing something wrong here as i'm getting the wrong value )... but i'm sure it's not the only or correct way to do it.

Is there any kind of way of saying:

For Each Column
Get column i from the range
Get the average of that column.
Put it in an array.

Then have a loop going through replacing the empty cells with that value?

OR, even better a way of just putting in a formula into the empty cells saying
= AVERAGE(Column from Range)
I tried this using AVERAGE(Range.Columns,1) and it didn't work, as it is of type Column I believe which average won't work with.

The easiest way to do this I will mark as the answer. It needs to use a macro though rather than just being manually putting the formula in, as it's for a lot of data on a lot of sheets.

Thanks.

Upvotes: 1

Views: 3011

Answers (3)

brettdj
brettdj

Reputation: 55682

You can use a formula to act on just the empty cells as you had requested
(Note - this method presumes the cells are truly empty, i.e. not containing formulae that evaluate to "" etc).

This code averages any non-blank numerical cells in A1:A10000, and then populates the blanks in A1:A10000. Then B1:B10000 and so on

Sub FillBlanks()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:D10000")
On Error Resume Next
For Each rng2 In rng1.Columns
    rng2.SpecialCells(xlCellTypeBlanks).Value = Application.WorksheetFunction.Average(rng2.Columns(1))
Next
End Sub

Upvotes: 0

Heman
Heman

Reputation: 118

Say, the range passed is abc

notBlanks = abc.Rows.Count - WorksheetFunction.CountBlank(abc.Columns(i))
avg = WorksheetFunction.Sum(abc.Columns(i)) / notBlanks

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

This will a selected range, per column, filling in blank cells with the average on non-blank cells.

It addresses your speed concern, by minimising access to cells in the loops (tested on about 5,000,000 cells, took about 10s) (could possibly be improved further at the expence of more complex code)

Preserves any formulas

Sub FillInAverage()
    Dim rng As Range
    Dim col As Range
    Dim a As Variant
    Dim dat As Variant
    Dim i As Long

    Set rng = Selection
    For Each col In rng.Columns
        a = Application.WorksheetFunction.Average(col)
        dat = col.Formula
        For i = 1 To UBound(dat, 1)
            If Len(dat(i, 1)) = 0 Then
                dat(i, 1) = a
            End If
        Next
        col.Formula = dat
    Next
End Sub

Upvotes: 2

Related Questions