kurt0101
10-23-2008, 07:48 AM
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
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