+ Reply to Thread
Results 1 to 3 of 3

Compare two Range Cell

  1. #1
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    787

    Compare two Range Cell

    I have this situation :
    Diameter 35, and Tolerance max=0 and min=-0.014
    I need to look for this Diameter in another Table, and find the moust appropiate value to my Tolerance
    See attached for better description (I hope)
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,251

    Re: Compare two Range Cell

    Hi,

    While the format of data seems easy for human, I think it is not so easy for excel.

    And especially if for some reason you keep numbers as texts (see formatting of cells in your file - a lot of them are neither General, nor Number, but Text).
    Next point for diameters 10-20 you doi not have tolerances, but texts max/min.

    I thought it is not for reason, but it "just happened" so restored numbers their numerical nature :-)

    My suggestion (for sake of functions readability) is to use helper cells. For instance in G15 (below table):
    =INDEX(G4:G8,MATCH($A3,$E4:$E8))
    and copy right. This will pick up from the table interesting row.
    This would be enough to have (rather complex) arrray formulas in D11, F11 and F12. as presented in Step 1 sheet (it is ready to use as is).

    But looking for simplicity I'd do (as shown in Step 2X sheets)
    - move cells with min tolerances below their max counterparts (H15 to G16 etc) see Step 2a,
    - and then move cells them left (I15:16 to H15:16, and the same with next column) See Step 2b
    - add headers in row 14
    - in row 17 (G17 and copy to right) add checking whether required tollerances fit inside tollerance for given class*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    value of 1000 or more will indicate that we felt out the field of tollerance
    - now finding values is super-easy, D11:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    F11 (and similar for F12):
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    So if it suits you you can delete first 2 shets and work just with this one.

    * Note that in Step 1 formulas I used "closest abs value" - formulas in D11, F11 and F12. It could be adjusted, but I think the second approach is better anyway, so left them as they were.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    Office 365 Version 2008 (July 2020)
    Posts
    787

    Re: Compare two Range Cell

    I would like to thank you, it worked out great

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 06-05-2014, 05:02 PM
  2. Replies: 3
    Last Post: 06-22-2012, 06:40 PM
  3. Replies: 6
    Last Post: 05-30-2012, 03:09 PM
  4. compare cell to a range
    By jcoshow in forum Excel General
    Replies: 9
    Last Post: 06-26-2011, 02:35 AM
  5. How to compare a cell to a range.
    By mandy02 in forum Excel General
    Replies: 4
    Last Post: 02-17-2008, 10:21 PM
  6. Way to compare one cell vs a range?
    By Rgaherty in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2007, 02:24 PM
  7. compare cell with a range
    By bagus in forum Excel General
    Replies: 6
    Last Post: 06-13-2006, 08:00 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1