If cell is N/A, how to replace it with the average value of the cells above and below

[ChrisPang1]

If the cell is N/A in excel, how can I replace this cell with the average value of the cells above and below in VBA? I would like to do this across the entire spreadsheet.


There are two situations:


1. B3:F3 - I would like to average these cells using B2:F2 and B4:F4 (e.g. B3 is the average of B2 and B4, C3 is the average of C2 and C4 etc)


2. B6:F6 and B7:F7 - I would like to average these cells using B5:F5 and B8:F8 (e.g. same logic here, B6 is the average of B5 and B8)
(I would not like to use B7:F7 to calculate my average values for B6:F6. Therefore, both B6 and B7 will be 7014.5 )


*Please bear in mind that in the actual data, there are multiple lines of N/A (e.g. B6:F40 could all be N/A)


Many thanks for any light you may shed!


With warmest regards,
Chris

[georgiboy]

Welcome to the forum, how about:

Sub FillGaps()    
    Dim rCell As Range, endRow As Long
    Dim abvValue, blwValue
    
    endRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each rCell In Range("B2:F" & endRow).Cells
        If rCell.Value = "N/A" Then
            abvValue = rCell.Offset(-1, 0).Value
            blwValue = NextData(rCell, Cells(endRow, rCell.Column))
            If blwValue <> "" Then
                rCell.Value = Application.Average(abvValue, blwValue)
            End If
        End If
    Next rCell
End Sub


Function NextData(CurrCell As Range, LastCell As Range)
    Dim sRng As Range, rCell As Range
    
    Set sRng = Range(CurrCell, LastCell)
    For Each rCell In sRng.Cells
        If IsNumeric(rCell.Value) Then
            NextData = rCell.Value
            Exit For
        End If
    Next rCell
End Function

Hope this helps

[ChrisPang1]

Very many thanks georgiboy for your prompt reply - this is much appreciated!!

I have tested your code in the sample data. Two minor follow-up questions if I may:
1. How could we ensure B6:F6 and B7:F7 all use the average values of B5:F5 and B8:F8?
In your code, B7:F7 use the average values of B6:F6 and B8:F8

2. Could you please advise how we should edit the code so that hidden N/A rows are not calculated?
There are none in this sample file but many rows will need to be hidden (not removed) in the actual file

Many thanks, for your help, in advance.

[georgiboy]

Hi there,

I may have over complicated things somewhat, maybe someone else has a simpler solution?

This will completely ignore hidden rows and from what I understand do what you need.

Global naCount As Long
Sub FillGaps()
    Dim rCell As Range, endRow As Long
    Dim abvValue, blwValue, tmpVal As String
    
    endRow = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each rCell In Range("B2:F" & endRow).Cells
        If Not rCell.EntireRow.Hidden Then
            If rCell.Value = "N/A" Then
                abvValue = rCell.Offset(-1, 0).Value
                blwValue = NextData(rCell, Cells(endRow, rCell.Column))
                If blwValue <> "" Then
                    tmpVal = Application.Average(abvValue, blwValue)
                    For x = 0 To naCount - 1
                        If Not rCell.Offset(x, 0).EntireRow.Hidden Then
                            rCell.Offset(x, 0).Value = tmpVal
                        End If
                    Next x
                End If
            End If
            naCount = 0
        End If
    Next rCell
End Sub

Function NextData(CurrCell As Range, LastCell As Range)
    Dim sRng As Range, rCell As Range
    
    Set sRng = Range(CurrCell.Offset(1, 0), LastCell)
    For Each rCell In sRng.Cells
        naCount = naCount + 1
        If IsNumeric(rCell.Value) And Not rCell.EntireRow.Hidden Then
            NextData = rCell.Value
            Exit For
        End If
    Next rCell
End Function

Hope this helps

[Aussiebear]

Actually I would be asking if the result is N/A, rather than replacing the result, wouldn’t you be better off determining why the result is N/A rather than the answer you were expecting?

[Paul_Hossler]

Q: I get the impression that the "N/A" is not the result of an Excel formula, but is flagging missing data??

Q2: Why the gap between row 9 and row 7585 in the attachment?

[ChrisPang1]

Actually I would be asking if the result is N/A, rather than replacing the result, wouldn’t you be better off determining why the result is N/A rather than the answer you were expecting?

It is simply because they were no transactions in those minutes and therefore spreadsheet populates N/A

[ChrisPang1]

1. Yes flagging missing data

2. I simply forgot to remove it!

[ChrisPang1]

Many thanks. Global naCount As Long shows an error and says that public cannot be a constant or fixed length.

Please could you advise what I should do?

[georgiboy]

Hmmm

I think if you are running this code from the sheet module then the global will need to be declared in a standard module.

Correct me if I'm wrong guys.

[Paul_Hossler]

If you're trying to fill in missing data, I'd use an interpolation technique and not just averaging

Capture.JPG

Option Explicit
'Assumes that first and last row has 100% data values

Sub FillInMissingData()
    Dim rAllData As Range, rData As Range, rNA As Range, rNAarea As Range, rBlank As Range
    Dim rKnownY As Range, rKnownX As Range
    
    
    
    Set rAllData = ActiveSheet.Cells(1, 1).CurrentRegion
    Set rData = rAllData.Cells(2, 2).Resize(rAllData.Rows.Count - 1, rAllData.Columns.Count - 1)
    
    Call rData.Replace("N/A", vbNullString, xlWhole, , False)
    On Error Resume Next
    Set rNA = rData.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    
    If rNA Is Nothing Then Exit Sub
    For Each rNAarea In rNA
        For Each rBlank In rNAarea.Cells
            With rBlank
                                
                Set rKnownX = Range(.End(xlUp).EntireRow.Cells(1), .End(xlDown).EntireRow.Cells(1))
                Set rKnownY = Range(.End(xlUp), .End(xlDown))
                ' excel 2016
                '.Value = Round(Application.WorksheetFunction.Forecast_Linear(.EntireRow.Cells(1).Value, rKnownY, rKnownX), 2)
                'previos
                .Value = Round(Application.WorksheetFunction.Forecast(.EntireRow.Cells(1).Value, rKnownY, rKnownX), 2)
            End With
        Next
    Next
End Sub

[p45cal]

I tend to agree with Paul about interpolation.
Take a very slightly different starting point, where what's being plotted is shaded light blue. Note the 1 minute difference between adjacent rows in the red box:
2018-07-24_163649.jpg


If we used plain averages to fill in the missing points we get:
2018-07-24_164000.jpg
The added points are shown in red. Note the jump to the second new point on the chart.


Personally, I'd prefer interpolation taking into acccount the date/time stamps:
2018-07-24_164213.jpg
Does Chris agree?

[p45cal]

…if Chris does agree, this routine handles hidden rows (treats them as if they didn't exist, regardless of what values they might hold) and doesn't attempt to fill anything in if the topmost/bottommost values are 'N/A'.
It's only half way through development - there are too many loops, too many steps - I'll make it leaner and meaner if Chris thinks it's a path he'd like to follow.

Sub blah()
'CopySheet1
With Range("A2:F8") ' <<< will need adjusting/automating.
  columnCount = .Columns.Count
  Set RngColm1 = .Columns(1).SpecialCells(xlCellTypeVisible)
  RowCount = RngColm1.Cells.Count
  ReDim vis(1 To RowCount, 1 To .Columns.Count)
  c = 0
  For Each colm In .Columns
    c = c + 1
    Set x = colm.SpecialCells(xlCellTypeVisible)
    r = 0
    For Each cll In x.Cells
      r = r + 1
      vis(r, c) = cll.Value
    Next cll
  Next colm
  For c = 2 To UBound(vis, 2)
    For r = 2 To UBound(vis) - 1
      If IsNumeric(vis(r - 1, c)) And vis(r, c) = "N/A" Then FillStart = r
      If IsNumeric(vis(r + 1, c)) And vis(r, c) = "N/A" Then Fillend = r
      If FillStart > 0 And Fillend > 0 Then
        Debug.Print c, FillStart, Fillend
        x1 = Empty: x2 = Empty
        x1 = CDbl(vis(FillStart - 1, 1)): x2 = CDbl(vis(Fillend + 1, 1))
        y1 = Empty: y2 = Empty
        y1 = vis(FillStart - 1, c): y2 = vis(Fillend + 1, c)
        For ro = FillStart To Fillend
          zz = Application.WorksheetFunction.Forecast_Linear(vis(ro, 1), Array(y1, y2), Array(x1, x2))
          If vis(ro, c) = "N/A" Then vis(ro, c) = zz
        Next ro
        FillStart = 0: Fillend = 0
      End If
    Next r
  Next c
End With
ro = 0
For Each cll In RngColm1.Cells
  ro = ro + 1
  'cll.Offset(, 1).Resize(, columnCount - 1).Select
  co = 1
  For Each celle In cll.Offset(, 1).Resize(, columnCount - 1).Cells
    co = co + 1
    'celle.Select
    If celle.Value = "N/A" Then celle.Value = vis(ro, co)
  Next celle
Next cll
End Sub

[ChrisPang1]

Many many thanks everyone - this is much appreciated!! Very very helpful!!