View original (French)

Closest to zero in Excel

BOGI35 -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello,
In Excel, you can use the following formula to find the value closest to zero in a matrix with 6000 rows (1 column):
=INDEX(A:A, MATCH(MIN(ABS(A1:A6000)), ABS(A1:A6000), 0))
Thank you

BOGI35
Configuration: Windows XP Internet Explorer 7.0

2 answers

Answer 1 / 2
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello,

Output from my attic:

The column is B2:B15 for the example

tries:

=IF(ABS(MAX(IF((B2:B15)<=0;(B2:B15))))<abs>0;(B2:B15))));MAX(IF((B2:B15)<=0;(B2:B15)));MIN(IF((B2:B15)>0;(B2:B15))))

array formula to be validated with Ctrl+Shift+Enter

Michel</abs>
1
BOGI35
 
Thank you Michel,
I just got back from the meeting, I will try your formula tomorrow morning.


THANK YOU AGAIN


BOGI35
0
Answer 2 / 2
LePierre Posted messages 249 Status Member 338
 
Hello

use the MIN function
for example in a cell you write: =MIN(A16:A21) to get the minimum value from the range A16:A21

see you later
0
BOGI35
 
First of all, thank you for your response,
I tried this function, however, in my matrix table, I have values that are negative (ranging from -3,000 to +3,000). What I can't seem to do is display in a cell the value closest to zero. The "Min" function gives me the smallest value (which is not necessarily the closest to zero).


BOGI35
0
LePierre Posted messages 249 Status Member 338 > BOGI35
 
Hello

I didn't know there were negative values
use the following array function:
for example, in a cell you type: =MIN(ABS(A16:A21)) to get the minimum value of the range A16:A21
you need to validate the formula by pressing Ctrl Shift and Enter
the formula will appear in the form: {=MIN(ABS(A16:A21))}

see you soon
0
BOGI35 > LePierre Posted messages 249 Status Member
 
Thank you,
I will try your formula tomorrow morning (I just got back from a meeting)
The one suggested by Michel is a bit more complex; I will test it as well and will keep you informed.

Thank you again for your availability



BOGI35
0
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > BOGI35
 
Hello everyone,

Indeed, Lepierre's formula is significantly better than my complicated setup!!!
Thank you to Lepierre
Best regards

Michel
0