Find the difference between numbers amongst blank cells

[pwillia]

Hi all,

I have a feeling this problem is not too complicated, but i'm struggling to find a formula to handle it, description below:

Column A contains numbers and #N/A or blank values. If there is a number in cell A1, I would like cell B1 to trigger a formula where it will search down column A to find the next number (skipping blank or #N/A values). Once it has found the next number I would like it to calculate the difference. I would like to implement this down column B. There will be inconsistent gaps between the number values in column A.

Crude example below:

[TABLE="width: 119"]
<tbody>[TR]
[TD]Values:[/TD]
[TD]Difference:[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]-10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]-5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please let me know what you think is best here, thank you for your time,

Pete

 

[bhos123]

Filter out NAs and blanks, apply the formula, you will get what you want.Copy them paste it somewhere else and paste them back after removing the filters.

 

[pwillia]

Hi Bhos123,

Thank you for responding to my question, although I would like to apply a formula which would not require me to filter the data before and after each time the data is refreshed.

Let me know if there are any other ideas out there, would this perhaps require using VBA, I feel this might be a tricky one to handle using formulas alone.

Thanks again,

Pete

 

[bhos123]

Sub Test()
Dim LastCol As Integer
Dim lastrow As Integer
Dim p As Long
Cells(1, 2).Value = "Difference"
Application.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

For i = 2 To lastrow
If Cells(i, 1) <> "" Then
For j = i + 1 To lastrow
If Cells(j, 1) <> "" Then
Cells(i, 2).Value = Cells(i, 1).Value - Cells(j, 1).Value
Exit For
End If
Next j

End If

Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Try this formula, copied down. Where I have $20 in the formula, make sure that is a row somewhere below the bottom of your data.

Excel Workbook
	A	B
1	10	5
2
3	5	-13
4	#N/A
5
6
7	18	15
8	3	-2
9	5	1
10
11	#DIV/0!
12
13	4
14
Differences

 

[pwillia]

Bhos123 thank you for the code structure, I have gone through it and now better understand the mechanics of VBA for this exercise.

Peter_SSs the formula you have given is spot on for this situation - thank you. I have not seen the index match function used in this way before.

Appreciate you time on this both, this is now solved.

Pete

 

[Peter_SSs]

Glad you have a successful outcome. Thanks for letting us know.