Column formulas to subtract value from value

[DBane]

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/~alli...ersubtract.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

[DBane]

Thank you,
this gives the #NAME? result.

"Mallycat" wrote:

>
> 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/~alli...ersubtract.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
>
>
> --
> Mallycat
> ------------------------------------------------------------------------
> Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
> View this thread: http://www.excelforum.com/showthread...hreadid=552964
>
>

[DBane]

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.

"VBA Noob" wrote:

>
> 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
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=552964
>
>