PDA

View Full Version : [SOLVED] If cell is N/A, how to replace it with the average value of the cells above and below



ChrisPang1
07-22-2018, 06:36 PM
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
07-22-2018, 11:03 PM
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
07-23-2018, 01:04 AM
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
07-23-2018, 07:02 AM
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
07-23-2018, 04:16 PM
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
07-24-2018, 05:53 AM
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
07-24-2018, 06:12 AM
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
07-24-2018, 06:18 AM
1. Yes flagging missing data

2. I simply forgot to remove it!

ChrisPang1
07-24-2018, 06:27 AM
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
07-24-2018, 07:02 AM
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
07-24-2018, 07:21 AM
If you're trying to fill in missing data, I'd use an interpolation technique and not just averaging

22613




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).Val ue, rKnownY, rKnownX), 2)
'previos
.Value = Round(Application.WorksheetFunction.Forecast(.EntireRow.Cells(1).Value, rKnownY, rKnownX), 2)
End With
Next
Next
End Sub

p45cal
07-24-2018, 08:43 AM
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:
22615


If we used plain averages to fill in the missing points we get:
22616
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:
22617
Does Chris agree?

p45cal
07-24-2018, 09:04 AM
…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
07-29-2018, 01:32 AM
Many many thanks everyone - this is much appreciated!! Very very helpful!! : pray2:: pray2: