+ Reply to Thread
Results 1 to 5 of 5

if the difference is less than or greater than 50 return value

  1. #1
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    28

    if the difference is less than or greater than 50 return value

    Hi all

    I have an array formula here which looks for a difference between various sets of data and returns values if there is a difference. Could someone help me modify this so rather than having <> to return an absolute difference it will read, if there is a difference between the values of 10 or 50 or 100 return the value

    =CONCATENATE(IFERROR(INDIRECT("a"&SMALL(IF($D$2:$D$29<>$J$2:$J$29,ROW($J$2:$J$29),""),ROW(A1))),"")," ",(IFERROR(INDIRECT("B"&SMALL(IF($D$2:$D$29<>$J$2:$J$29,ROW($J$2:$J$29),""),ROW(B1))),"")))

    Thanks to anyone to can offer help!
    Attached Files Attached Files
    Last edited by deancorleone; 10-01-2013 at 11:24 PM. Reason: better explanation of issue

  2. #2
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: if value have a difference of 10/20/30 etc then return value

    Just to add to this, incase the description wasnt that great, I am looking to change the formula from saying if there is an absolute difference to .. if the difference is less than or greater than 50 return value

  3. #3
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: if the difference is less than or greater than 50 return value

    anyone got any ideas? i would greatly appreciate.

    Thanks

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: if the difference is less than or greater than 50 return value

    Try this array formula in M7

    =IFERROR(INDEX(A$1:A$29&" "&B$1:B$29,SMALL(IF(ABS(D$2:D$29-J$2:J$29)>=50,ROW(D$2:D$29)),ROWS(M$7:M7))),"")

    confirm with CTRL+SHIFT+ENTER and copy down the column

    That will only return items where the difference is >= 50 - change as required
    Audere est facere

  5. #5
    Registered User
    Join Date
    02-25-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: if the difference is less than or greater than 50 return value

    stunned ! thank you so much ! I have never used ABS in a formula so ill get onto google to see what it's doing. Thanks so much!

+ 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. [SOLVED] Macro to check data for matches and return a difference
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-27-2013, 10:59 AM
  2. Formula: Return difference value in different cell?
    By Shokra in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2013, 10:42 AM
  3. Compare letters in two cells, return difference
    By mkvassh in forum Excel General
    Replies: 2
    Last Post: 04-11-2011, 02:51 AM
  4. Return minutes difference between 2 times
    By reggie1000 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-17-2009, 11:57 AM
  5. Match 2 values and return Difference
    By rkeane7 in forum Excel General
    Replies: 2
    Last Post: 07-31-2008, 08:41 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