Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 ! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I pick up a nearest value to zero
Thank you, teethless mama. It works just fine ...
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you round up to nearest 10??? | Excel Discussion (Misc queries) | |||
Round up to nearest 10 | Excel Worksheet Functions | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) | |||
looking up nearest value | Excel Worksheet Functions | |||
looking up nearest value | Excel Worksheet Functions |