Find and subtract

[kurt0101]

hi. suppose i have a column of numbers.
ex.
34
56
78
34
12
90
67
23

what i want to do is find the the minimum number in the column which is easy to do =MIN(A1:A9) = 12
BUT THEN I want to subtract:

all numbers below 12, including 12, from each other.
example output
=12-90
=90-67
=67-23
and all numbers above 12 from each other, including 12.
example output
=12-34
=12-78
=56-34

of course i could do this if i just had one or two columns or if the minimum value was always found in, say, A5.
but i have hundreds of columns and each minimum value of the column is found in different cells.

is there a way to tell excel to find the minimum value and then locate the cell right above/below it and start subtracting from that point on?

something like
=MIN(A1:A9) - Cell right below MIN(A1:A9)
=Cell right below MIN(A1:A9) - second cell below MIN(A1:A9)
etc

and
=MIN(A1:A9) - Cell above MIN(A1:A9)
=Cell right above MIN(A1:A9) - second cell above MIN(A1:A9)
etc

thank you

[georgiboy]

Crude but it may be a starting point, it assumes that the data in column A is constant.

Sub Subtract()
Dim Mini As Integer, rCell As Range, MyRange As Range
Dim MiniRow As Integer

Mini = WorksheetFunction.Min(Range("A:A"))



Set MyRange = Range("A1:A500")
For Each rCell In MyRange.Cells
If rCell = Mini Then
MiniRow = rCell.Row
GoTo jump1
End If
Next rCell


jump1:
Range("B" & MiniRow).Select
Do
ActiveCell.Value = ActiveCell.Offset(, -1).Value - ActiveCell.Offset(-1, -1).Value
ActiveCell.Offset(-1).Select
Loop While ActiveCell.Row > 1

Range("B" & MiniRow).Select
Do
ActiveCell.Value = ActiveCell.Offset(, -1).Value - ActiveCell.Offset(1, -1).Value
ActiveCell.Offset(1).Select
Loop While ActiveCell.Offset(, -1).Value <> ""



End Sub
Hope this helps

[kurt0101]

hi. thanks that helps.
is there any command/function that adds one column to the next column above or below it.
something like
= A10 - cell before A10 or =A10 - cell above A9

thanks

[david000]

Why not sort?

Sub Macro1()
Dim lastrow As Long, Col As String
Col = "B"
With ActiveSheet
lastrow = .Cells(.Rows.Count, Col).End(xlUp).Row
Range(Col & 1).Resize(lastrow).Sort Key1:=Range(Col & 1), Order1:=xlAscending, Header:=xlGuess
Range(Col & 1).Offset(, 1).EntireColumn.Insert
Range(Col & 1).Offset(1, 1).Resize(lastrow - 1).FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
End With
End Sub

[georgiboy]

You could try something like this,

=IF(ROW(A2)=(MATCH( MIN(A:A ),A:A, 0)),A2-A1,IF(ROW(A2)<(MATCH( MIN(A:A ),A:A, 0)),A2-A1,IF(ROW(A2)>(MATCH( MIN(A:A ),A:A, 0)),A2-A3)))

Hope this helps