how can I pick up a nearest value to zero
 

In a range of cells, I have negative and positive numbers. How can I obtain,
in another cell, the value from my range that is negative AND nearest to
zero, and, in another cell, the positive number AND nearest to zero ? Example
: in my range of cells, I have : -10 , -7 , -3 , 1 , 4 , 7 , 9. I need to
extract from this range values -3 and 1. The values in my range are changing
everytime, because they are provided by formulas.
Thank you !

how can I pick up a nearest value to zero
 

This might suffice ..
Assuming data as posted is in A1:A7, in ascending order (as posted)
in say, B1 and in C1
=INDEX(A1:A7,MATCH(0,A1:A7))
=INDEX(A1:A7,MATCH(0,A1:A7)+1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"aiurelbn" wrote:
In a range of cells, I have negative and positive numbers. How can I obtain,
in another cell, the value from my range that is negative AND nearest to
zero, and, in another cell, the positive number AND nearest to zero ? Example
: in my range of cells, I have : -10 , -7 , -3 , 1 , 4 , 7 , 9. I need to
extract from this range values -3 and 1. The values in my range are changing
everytime, because they are provided by formulas.
Thank you !

how can I pick up a nearest value to zero
 

=MAX(IF(A1:A7<0,A1:A7))
=MIN(IF(A1:A70,A1:A7))

ctrl+shift+enter, not just enter


"aiurelbn" wrote:

In a range of cells, I have negative and positive numbers. How can I obtain,
in another cell, the value from my range that is negative AND nearest to
zero, and, in another cell, the positive number AND nearest to zero ? Example
: in my range of cells, I have : -10 , -7 , -3 , 1 , 4 , 7 , 9. I need to
extract from this range values -3 and 1. The values in my range are changing
everytime, because they are provided by formulas.
Thank you !

how can I pick up a nearest value to zero
 

Thank you, teethless mama. It works just fine ...

how can I pick up a nearest value to zero
 

The simpler, non-array index/match option would have worked fine too, if the
data is sorted in ascending order, as you had originally posted, and as
pointed out in the response. Do not dismiss the option as not helpful.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---