this is killing me. INDEX/MATCH "closest to X" in a smart way?

  • Thread starter Johnny Ryefield
  • Start date
J

Johnny Ryefield

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".

thanks. J

is there a way to upload attachments here? I'm sure it'd make it a lot easier to understand
 
J

Johnny Ryefield

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".



thanks. J



is there a way to upload attachments here? I'm sure it'd make it a lot easier to understand

Link to sample spreadsheet:
http://www.ozgrid.com/forum/attachment.php?attachmentid=56700&d=1380710992
 
C

Claus Busch

Hi,

Am Wed, 2 Oct 2013 03:55:46 -0700 (PDT) schrieb Johnny Ryefield:

I have no permission to download the file.

Have a try (for D = 1):
=IF(D2:D1000=1,INDEX(B2:B1000,MATCH(MIN(ABS(C2:C1000+0.25)),ABS(C2:C1000+0.25),0))-INDEX(B2:B1000,MATCH(MIN(ABS(C2:C1000-0.25)),ABS(C2:C1000-0.25),0)))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
 
J

Johnny Ryefield

Hi,



Am Wed, 2 Oct 2013 03:55:46 -0700 (PDT) schrieb Johnny Ryefield:







I have no permission to download the file.



Have a try (for D = 1):

=IF(D2:D1000=1,INDEX(B2:B1000,MATCH(MIN(ABS(C2:C1000+0.25)),ABS(C2:C1000+0.25),0))-INDEX(B2:B1000,MATCH(MIN(ABS(C2:C1000-0.25)),ABS(C2:C1000-0.25),0)))

and enter the array formula with CTRL+Shift+Enter





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Clause - here's a dropbox link! and thank you in advance!!
https://www.dropbox.com/s/q9srmoawn4wetqg/forum delta.xlsx
 
C

Claus Busch

Hi Johnny,

Am Wed, 2 Oct 2013 04:45:03 -0700 (PDT) schrieb Johnny Ryefield:
Also, your formula seems to be working somehow (still trying to figure out how you did it lol), but it cannot be dragged down, which is a problem. I tried changing your "IF(D2:D1000=1" to IF(D2:D1000=ROW(A1) and then drag the formula down but it gives me "FALSE" for all other cells beneath. :(

for D=1 try:
=INDEX(B2:B1000,MATCH(MIN(IF(D2:D1000=1,ABS(C2:C1000-0.25))),E2:E1000,0))-INDEX(B2:B1000,MATCH(MIN(IF(D2:D1000=1,ABS(C2:C1000+0.25))),E2:E1000,0))
and enter with CTRL+Shift+Enter


Regards
Claus B.
 
C

Claus Busch

Hi Johnny,

Am Wed, 2 Oct 2013 04:45:03 -0700 (PDT) schrieb Johnny Ryefield:
Also, your formula seems to be working somehow (still trying to figure out how you did it lol), but it cannot be dragged down, which is a problem. I tried changing your "IF(D2:D1000=1" to IF(D2:D1000=ROW(A1) and then drag the formula down but it gives me "FALSE" for all other cells beneath. :(


please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for file "forum delta"
The new formula works and you can drag it down.


Regards
Claus B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top