how can I pick up a nearest value to zero

A

aiurelbn

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 !
 
M

Max

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)
 
T

Teethless mama

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

ctrl+shift+enter, not just enter
 
M

Max

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top