subtraction in a list of numbers that resets with a label.
 

I have a long list of data in a column with tags or labels at different
intervals in the list. I am trying to subtract each row from the data
point just below the lable. Then when a new label is found the list my
calculations continue but based on the value after the newly found
lable. Can that be done with functions?

For example:

label NA
1 0
3 2
7 6
9 8
label NA
5 0
9 4
15 10
17 12

subtraction in a list of numbers that resets with a label.
 

"gtslabs" wrote in message
oups.com...
I have a long list of data in a column with tags or labels at different
intervals in the list. I am trying to subtract each row from the data
point just below the lable. Then when a new label is found the list my
calculations continue but based on the value after the newly found
lable. Can that be done with functions?

For example:

label NA
1 0
3 2
7 6
9 8
label NA
5 0
9 4
15 10
17 12

This is only half an example: give the result you
want to get from operation.

Bruno

subtraction in a list of numbers that resets with a label.
 

The result is in the right hand column.
Thanks
Steve

subtraction in a list of numbers that resets with a label.
 

If your input data starts in A1 and you'll allow a 'helper' column B...
In B2: =IF(ISNUMBER(A1),B1,A2) <-- this tracks the value to subtract
In C2: =IF(ISNUMBER(A2),A2-B2,"NA") <-- this becomes your result
--Bruce

"gtslabs" wrote:

The result is in the right hand column.
Thanks
Steve

subtraction in a list of numbers that resets with a label.
 

"gtslabs" wrote in message
ups.com...
The result is in the right hand column.
Thanks
Steve

I think it can't be done without a helper column B
as suggested by gtslabs.

Or it can be done with VBA code.
Do you want to have this too?

Bruno

subtraction in a list of numbers that resets with a label.
 

"Bruno Campanini" wrote in message
...
Or it can be done with VBA code.
Do you want to have this too?

Bruno

Here it is:

===========================
Sub StrangeCalculations()
Dim SourceRange As Range, TargetRange As Range
Dim StartRange As Range, i, j As Long
Dim PreviousItem

' Definitions
' -----------------------------
Set StartRange = [Sheet2!A281]
Set TargetRange = [Sheet2!B281]
' -----------------------------

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
On Error GoTo ErrHandler

Set SourceRange = Range(StartRange, StartRange.End(xlDown))
ReDim TempArray(1 To SourceRange.Rows.Count)

For Each i In SourceRange
j = j + 1
If Not IsNumeric(i) Then
TargetRange.Offset(j - 1, 0) = "NA"
PreviousItem = i
ElseIf IsNumeric(i) And Not IsNumeric(PreviousItem) Then
TargetRange.Offset(j - 1, 0) = 0
PreviousItem = i
ElseIf IsNumeric(i) And IsNumeric(PreviousItem) Then
TargetRange.Offset(j - 1, 0) = i - PreviousItem
End If
Next

Exit_Sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Procedu StrangeCalculations" & vbCrLf & _
ThisWorkbook.FullName
Resume Exit_Sub

End Sub
==================================

Regards
Bruno