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

[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

 

[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

 

[Claus Busch]

Hi,

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

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

I have no permission to download the file.

Have a try (for D = 1):
=IF(D21000=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.

 

[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(D21000=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

 

[Johnny Ryefield]

Clause - here's a dropbox link! and thank you in advance!!

https://www.dropbox.com/s/q9srmoawn4wetqg/forum delta.xlsx

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(D21000=1" to IF(D21000=ROW(A1) and then dragthe formula down but it gives me "FALSE" for all other cells beneath.

 

[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(D21000=1" to IF(D21000=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(D21000=1,ABS(C2:C1000-0.25))),E2:E1000,0))-INDEX(B2:B1000,MATCH(MIN(IF(D21000=1,ABS(C2:C1000+0.25))),E2:E1000,0))
and enter with CTRL+Shift+Enter


Regards
Claus B.

 

[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(D21000=1" to IF(D21000=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.