Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Compare cells within a range for a specific difference?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Surely this is possible but i am lost. I have tried a few different ways to accomplish this but run into the "Formula is too long" scenario. Here is what i am trying to do:

    A1:A16 contains the data. I want to be able to compare each cell within the range to all of the other cells within the same range for a difference of <.630 . For example, compare A1 to A2--A16, A2 to A1--A16, and so on. If the difference is encountered then some sort of flag would be raised whether it be cell formatting or a return value of TRUE/FALSE to an adjacent cell. Any help appreciated.

    Waxaholic

    [ This Message was edited by: Waxaholic on 2002-04-11 13:28 ]

  2. #2
    New Member
    Join Date
    Feb 2002
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can send the sheet or mockup sheet for me to have a look at if you like?
    andy.gee@blueyonder.co.uk

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Enter the array formula...

    {=OR(ABS(A1-IF(ROW($A$1:$A$16)<>ROW(A1),$A$1:$A$16))<0.63)}

    ...into B1 and copy down. Array formulas must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

    Waxaholic

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-11 20:32, Waxaholic wrote:
    Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

    Waxaholic
    Enter this in a cell

    =((ABS(A1:A16-TRANSPOSE(A1:A16)))<0.630)+0

    Activate the cell of the formula, select a range of 16 by 16 while in this cell, and hit control+shift+enter. You'll get a matrix of 1 and/or 0's. Not sure though whether it gives what you want.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 20:32, Waxaholic wrote:
    Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

    Waxaholic
    Provide some representative sample data and the expected results!

    [ This Message was edited by: Mark W. on 2002-04-15 15:00 ]

  7. #7
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Aladin,
    I made a simple model of 5x5 cells and it does not seem to work. Probably I do not understand your instructions:


    Enter this in a cell
    =((ABS(A1:A16-TRANSPOSE(A1:A16)))<0.630)+0
    Is this in any cell?


    Activate the cell of the formula, select a range of 16 by 16 while in this cell, and hit control+shift+enter. You'll get a matrix of 1 and/or 0's. Not sure though whether it gives what you want.
    How can I activate this cell and in the same time select another range of cells? Is it by pressing ALT ? Any way it is not working.

    Thanks in advance

    Eli


    [ This Message was edited by: eliW on 2002-04-15 09:56 ]

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,658
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    On 2002-04-15 09:55, eliW wrote:
    Hi Aladin,
    I made a simple model of 5x5 cells and it does not seem to work. Probably I do not understand your instructions:



    Enter this in a cell
    =((ABS(A1:A16-TRANSPOSE(A1:A16)))<0.630)+0
    Is this in any cell?

    Yes, a cell outside the data range.


    Activate the cell of the formula, select a range of 16 by 16 while in this cell, and hit control+shift+enter. You'll get a matrix of 1 and/or 0's. Not sure though whether it gives what you want.
    How can I activate this cell and in the same time select another range of cells? Is it by pressing ALT ? Any way it is not working.

    [ This Message was edited by: eliW on 2002-04-15 09:56 ]


    Eli,

    You just type the formula in the activated/selected cell and hit enter. You'll get a #VALUE! error. Now select an area 16 by 16, starting from this formula cell and hit control+shift+enter to get the matrix of results I mentioned.

    I must say that, as I noted, I'm not sure whether this meets the specs. Mark is already asking for more info.

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-04-15 10:52 ]

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-11 20:32, Waxaholic wrote:
    Ok, the formula above works fine for the A1 to A1:A16 comparisons. A2 to A1:A16 comparisons don't work at all. They just return the value for the A1 to A1:A16 comparison. Regardless of what i do they contine to return values based on A1. I created a failure for the A1 comparison and it worked fine. I then created a failure for the A2 comparison and it doesn't work. Any ideas? Thanks.

    Waxaholic
    Perhaps this "tweak" will suffice...

    {=OR(ABS(A1-IF(ROW($A$1:$A$16)<>ROW(A1),$A$1:$A$16,A1+0.63))<0.63)}

    If A1:A16 contains...

    {0.1;0.9;2;1.6;3;4;5;6;6.4;8;9;10;11;12;13;14}

    ...and, this array formula is entered into B1 and copied down to B16 it'll produce...

    {FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

    B3 and B4 are TRUE because |2-1.6| < 0.63.
    B8 and B9 are TRUE because |6-6.4| < 0.63.

    All other values in column have an absolute difference greater than 0.63.

    BTW, the formula insures that when a value is compared to itself it will NEVER return TRUE; otherwise, the results for the entire list would always be TRUE.

    Your problem statement suggested that A1 should be compared to A2:A16 and A2 should be compared to A1:A16. In fact, A1 should be compared to A2:A16, A2 should be compared to A1,A3:A16, etc.

    [ This Message was edited by: Mark W. on 2002-04-16 07:16 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •