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

  1.
    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
    1. BOGI35
       
      Thank you Michel,
      I just got back from the meeting, I will try your formula tomorrow morning.


      THANK YOU AGAIN


      BOGI35
      0
  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
    1. 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
      1. 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
      2. 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
      3. 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