first find value according to text, and later on do division of that

[adpatel]

I have excel sheet where certain values mentioned, here it's explained briefly so that it will be easy to understand the requirement

1. symbol=stock name
2. expiry date=time of contracts expire
3. strike price= numerical value
4. ce=call
5. pe=put
6. oi=open int.

now, for every 1 strike price, there can be 2 values, i.e 1. call & 2. put and different expiry dates

for e.g. expiry date= 30-07-2020, 100 call oi = 5000, 100 put oi = 15000,

expiry date= 28-08-2020, 100 call oi = 8000, 100 put oi = 5000,

so, what i want to do is, take same expiry and same strike price put oi and do division of that with same expiry and same strike price call oi

so, in case of 30-07-2020 expiry 100 strike price final output should be 3 (15000/5000),

and for 28-08-2020 expiry 100 strike price final output should be 0.63 (5000/8000).

problem here I faced is, I have all above mentioned values in different columns so,how to find same strike with same expiry with same stock name and divide them accordingly?

i have tried concatenate function but got stuck and won't be able to understand how to use it,

here below screenshot attached,

Kindly please help with this issue, any help will be highly appriciated.

 

[KRice]

Let me know if something like this is what you had in mind:

Book2
	A	B	C	D	E	F	G	H
1	INSTR.	SYMBOL	EXPIRY	STRIKE_P	CE/PE	OI		FINAL OUTPUT
2	OPTSTK	ACC	31-10-2019	1660	CE	10400		0.076923077
3	OPTSTK	ACC	31-10-2019	1940	CE	0
4	OPTSTK	ACC	31-10-2019	1960	CE	0
5	OPTSTK	ACC	31-10-2019	1660	PE	800
6	OPTSTK	ACC	31-10-2019	1680	PE	0
7	OPTSTK	ACC	31-10-2019	1700	PE	1600
8		BBB	30-07-2020	100	CE	5000		3
9		BBB	30-07-2020	100	PE	15000
10		CCC	28-08-2020	100	CE	8000		0.625
11		CCC	28-08-2020	100	PE	5000
Sheet3

Cell Formulas
Range	Formula
H2:H11	H2	=IFERROR(IF(E2="CE",INDEX($F$2:$F$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-ROW($B$1))/(($B$2:$B$11=B2)*($C$2:$C$11=C2)*($D$2:$D$11=D2)*($E$2:$E$11="PE")),1)),"")/F2,"")

This places the ratio beside the "CE" part of the pair.

 

[adpatel]

Yes, bro, that's the exactly something what I was looking for, any word of thx won't be able to discribe my greatfulness for you, thank you so much for putting efforts and taking out time from your buzy shedule, GOD BLESS YOU

 

[adpatel]

@KRice, here 1 small issue with this formula is, if it's PE, it doesn't do anything, actully it was my mistake, because I didn't mentioned/explain properly,
but if it's PE than also, the same formula should be apllied, in above e.g. in cell "H5", there should be 0.0769

I have tried following formula but it does actully ce oi/pe oi, instead this should be pe oi/ce oi

=IFERROR(IF(OR(E5="CE",E5="PE"),INDEX($M$4:$M$45013,AGGREGATE(15,6,(ROW($B$4:$B$45013)-ROW($B$3))/(($B$4:$B$45013=B5)*($C$4:$C$45013=C5)*($D$4:$D$45013=D5)*($E$4:$E$45013="PE")),1)),"")/M5,"-")

pls, help with this

(note:- in my actual worksheet, oi column is "M", and data points approx till 45013 hence, accordingly I have made the changes in formula)

 

[KRice]

It sounds like you want the same ratio PE/CE to appear for both parts of any matching pairs?
I've reworked the formula using some internal IF statements to flip numerators and denominators so that a single formula can be used. Give this a try:

Book2
	A	B	C	D	E	F	G	H
1	INSTR.	SYMBOL	EXPIRY	STRIKE_P	CE/PE	OI		FINAL OUTPUT
2	OPTSTK	ACC	31-10-2019	1660	CE	10400		0.076923077
3	OPTSTK	ACC	31-10-2019	1940	CE	0
4	OPTSTK	ACC	31-10-2019	1960	CE	0
5	OPTSTK	ACC	31-10-2019	1660	PE	800		0.076923077
6	OPTSTK	ACC	31-10-2019	1680	PE	0
7	OPTSTK	ACC	31-10-2019	1700	PE	1600
8		BBB	30-07-2020	100	CE	5000		3
9		BBB	30-07-2020	100	PE	15000		3
10		CCC	28-08-2020	100	CE	8000		0.625
11		CCC	28-08-2020	100	PE	5000		0.625
Sheet3

Cell Formulas
Range	Formula
H2:H11	H2	=IFERROR(INDEX($F$2:$F$11,AGGREGATE(15,6,(ROW($B$2:$B$11)-ROW($B$1))/(($B$2:$B$11=B2)*($C$2:$C$11=C2)*($D$2:$D$11=D2)*($E$2:$E$11=IF(E2="CE","PE","CE"))),1))^IF(E2="CE",1,-1)*(F2^IF(E2="CE",-1,1)),"")

 

[KRice]

Please check something for me...the ranges you've adjusted suggest that your tabular data occupy rows 4 to 45013, so for row 4, I think the formula would look like this:

=IFERROR(INDEX($M$4:$M$45013,AGGREGATE(15,6,(ROW($B$4:$B$45013)-ROW($B$3))/(($B$4:$B$45013=B4)*($C$4:$C$45013=C4)*($D$4:$D$45013=D4)*($E$4:$E$45013=IF(E4="CE","PE","CE"))),1))^IF(E4="CE",1,-1)*(M4^IF(E4="CE",-1,1)),"")

 

[adpatel]

Yes, perfect!, I must salute you, you are having great knowledge in excel, again lot of thx for again putting much efforts and taking out time from busy shedule

 

[KRice]

That's great...I'm happy to help.

 

[adpatel]

after applying formula, excel gets much slow sown, it shows, "calculating 4 threads" for saving excel also I need to wait for 5 min.

 

[KRice]

That's interesting. You might try the formula without the IFERROR wrapper to see if that speeds things up...so for the row 4 output:

=INDEX($M$4:$M$45013,AGGREGATE(15,6,(ROW($B$4:$B$45013)-ROW($B$3))/(($B$4:$B$45013=B4)*($C$4:$C$45013=C4)*($D$4:$D$45013=D4)*($E$4:$E$45013=IF(E4="CE","PE","CE"))),1))^IF(E4="CE",1,-1)*(M4^IF(E4="CE",-1,1))

...and then copy down. If that makes a noticeable difference, then it might be better to trap any errors earlier.