1

I have a set of values that look like this:

Time        DataSet1        DataSet2
00:10:00    15              27
00:20:00    #N/A            25
00:30:00    33              45
00:40:00    #N/A            #N/A
00:50:00    #N/A            25
01:00:00    #N/A            12

Now, I want to fill all #N/A values with the previous valid value in the table. For example: the value for DataSet1 at 00:40:00, 00:50:00 and 01:00:00 should be 33. How does one do that?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Cornel Verster
  • 1,664
  • 3
  • 27
  • 55
  • The #N/A are from a formula as I import timestamped data from other spreadsheets but there are not values for every time slot on the other sheets. No blanks need to be preserved, #N/A's just need to be replaced by the previous valid value. – Cornel Verster Mar 05 '15 at 14:17

5 Answers5

2

This should work for you:

=IF(ISERROR(<yourformulahere>),A1,<yourformulahere>)

Autofill from A2 to your last row.

Will only work if yourformula is "autofillable". (:

https://support.google.com/docs/answer/3093349

Tom K.
  • 1,020
  • 1
  • 12
  • 28
1

  1. Select A1 then tap F5 and when the GoTo dialog appears, click Special.

  2. Check Formulas and uncheck everything but Errors.

  3. Click OK.
  4. Type = then ↑ and finalize with Ctrl+Enter.

The errors should be converted to the value above.

  • 1
    @pnuts - Yes, I was relying on the sample data to determine that ambiguity. Perhaps the OP wanted `#DIV/0!` converted as well ...? –  Mar 05 '15 at 14:42
  • This really is a brilliant solution! I did however find problems in that it didn't always update all the values that were errors even though I used ctrl + enter. That may be a Excel bug or something though and not a problem with the solution. – Cornel Verster Mar 06 '15 at 13:21
1

Select the area you want to fix and run this macro:

Sub FixData()
    Dim r As Range
    For Each r In Selection
        If r.Text = "#N/A" Then
            r.Value = r.Offset(-1, 0).Value
        End If
    Next
End Sub

For example, before:

enter image description here

and after:

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

Just in case you prefer a macro-way:

Dim val As String
Dim col_to_check As Long, cnt As Long

col_to_check = 2 ' We check the 2nd column
cnt = 1 ' We'll use it to check rows below

For i = 1 To ActiveSheet.UsedRange.Rows.Count
 If IsError(Cells(i, col_to_check)) Then
   val = Cells(i - 1, col_to_check)
   Cells(i, col_to_check) = val
   While IsError(Cells(i + cnt, col_to_check))
     Cells(i + cnt, col_to_check) = val
     cnt = cnt + 1
   Wend
 End If
 i = i + cnt - 1
 cnt = 1
Next i
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
-2

We need to fill in "#N/A" cells with a formula =above cell address.

  1. CTRL+F
  2. Type #N/A
  3. Click Find All
  4. Highlight all with mouse as picture below.
  5. Click on one space above menu - see oval (do not close Find and Replace window)
  6. Start typing = and press Up key.
  7. Press CTRL+Enter
  8. Copy and paste all as text, to get rid of formulas.

enter image description here

zx8754
  • 52,746
  • 12
  • 114
  • 209
  • MS Office Professional Plus 2010. Version 14.0.7143.5000 32-bit – zx8754 Mar 05 '15 at 14:46
  • This should work on 2013 as well, check the steps, clarified it a bit more. Yes, also updated the picture. – zx8754 Mar 05 '15 at 14:54
  • if it is a formula, then copy paste as text, then follow my steps, or use @Jeeped `GoTo` solution. Logic is the same. – zx8754 Mar 05 '15 at 14:55