Column formulas to subtract value from value

[Guest]

I need a formula that subtracts value to value in a column. Some of the
cells are empty. Working in column a. A3:a:20
Example
a3=60.2 b3=a6-a3 (result 0.2)
a4=0
a5=0
a6=60.4 b6=a7-a6 (result 1.2)
a7=61.6 b7=a9-a7 (result -10.8)
a8=0
a9=50.8 b9=a11-a9 (result 10)
b10=0
b11=60.8 b11= It will be -60.8 . because it has nothing below to subtract.
can that be eliminated in the formula?

 

[VBA Noob]

Not sure why you jump from having formulas in Column A then at the end
having it in Column B.

If the forumla is always in Column B then sometime like this might
help

=IF(A12-A11=0,-A11,A12-A11)

Not sure how you know when to insert a formula with the data provided

VBA Noob

 

[Mallycat]

You can do it with a custom formula. I have written one for your below
and provided it in a spreadsheet here
http://members.optusnet.com.au/~allington65/Files/numbersubtract.xls

You use the formula like this (for your example)
=NumberSubtract(A3:$A$11)
Make sure you specify the start of the range as relative, and the end
of the range as absolute.

Copy the formula down.

Hope it works for you.

Matt




Function NumberSubtract(TargetRange As Range)
Dim myRange As Range
Dim myArray() As Double
Dim X, Y, TotalCells As Integer
Dim myAnswer As Double
Set myRange = TargetRange
TotalCells = myRange.Cells.Count
ReDim myArray(TotalCells)

For Each cell In myRange
X = X + 1
myArray(X) = cell.Value
Next cell

X = 0
For X = 1 To TotalCells
If myArray(X) > 0 Then
For Y = 1 + X To TotalCells
If myArray(Y) > 0 Then
myAnswer = myArray(Y) - myArray(X)
GoTo ExitHere:
End If
Next Y
End If
If TotalCells = 1 Then
myAnswer = -myArray(X)
Else
myAnswer = 0
End If
GoTo ExitHere:
Next X
ExitHere:
NumberSubtract = myAnswer

End Function

 

[Guest]

Thank you,
this gives the #NAME? result.

 

[Guest]

I was not clear enough with my question. I need a formula in b3:b20 that will
subtract the values in column a. Each value in column a, will subtract from
the last nonempty cell value in column.