Results 1 to 5 of 5

index/match "closest to X (value)"?

Threaded View

  1. #1
    Registered User
    Join Date
    05-15-2013
    Location
    Israel
    MS-Off Ver
    Excel 2013
    Posts
    70

    index/match "closest to X (value)"?

    Hi all
    So I have 4 columns, named "Type", "IV" "Delta" and "Counter". (you'll note that all "p" Type Deltas are negative and all "c" Deltas are positive).
    I need a formula to find me the IV of the "p" with the Delta value closest to 0.25 and subtract it from the the IV of the "c" with the Delta value closest to 0.25.

    To complicate things, the file is extremely big and the data is split into sets or "blocks" by the "Counter" column (D). so for all rows of block 1 D=1, for all rows of block 2 D=2 etc...

    so to break it down I need to:
    For each "Block" (ie, unique D value)
    1. find the IV value(B:B) for which A="c" AND for which ABS(0.25-ABS(C)) is zero or closest to zero.
    2. find the parallel with A="p"
    3. Subtract IV of "p" from IV of "c".

    forum delta.xlsx


    many many thanks..
    Last edited by ryefield; 10-02-2013 at 07:01 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Can you match "closest range" using sumproduct?
    By Stubrok in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-12-2015, 07:34 PM
  2. [SOLVED] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  3. Strings Variables in Range("A1").Formula = "=index/match" ?
    By nadnerb5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2012, 05:07 PM
  4. [SOLVED] Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2012, 04:09 PM
  5. Returning Closest Match between "y" and "z" to "X"
    By promotor in forum Excel General
    Replies: 4
    Last Post: 12-05-2011, 10:34 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