On Fri, 9 Sep 2005 07:53:05 -0700, "Cygnusx1"
wrote:

I have 27 different sets of data with each seperated by a few lines of text.
I need to be able to find the closest number to zero in comumn "B" for each
set(I have both negative and positive numbers) and then grab the numbers on
each side of it to copy to another location.
Thank you

I'm not sure exactly what you mean.

To find the number in column B closest to zero, where 'rng' is the range (e.g.
B2:B30):

=MIN(ABS(rng))

entered as an **array** formula by holding down <ctrl<shift when you hit
<enter.

To find the row number of this value:

=MATCH(MIN(ABS(rng)),rng,0)

also entered as an **array** formula.

I'm not sure what you mean by "grab the numbers on each side of it".

The number in the preceding row would be:

=INDEX(rng,MATCH(MIN(ABS(rng)),rng,0)-1)

and you can see HELP on the INDEX function to modify it for the number in the
next row; or for the number in the same row but on the columns "on each side".

Finally, so far as "copy to another location", that cannot be done with a
worksheet function. You would need to put one of the above INDEX/MATCH
constructs into the cell in which you want the result to appear, or use a VBA
solution.


--ron