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

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Untitled.png
    Untitled.png
    25.2 KB · Views: 7

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
Do you have other formulas in the worksheet? If so, what do they look like? I'm curious if you have some volatile functions or have referenced entire columns elsewhere.
 

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
Yes sir, total there are 45000 rows and almost 26 columns and out of 26 column almost 12 column filled up with formulas till 45000 rows, and with combination of formulas from simple as well as little complicated also, which takes value from another worksheet and perfoms calculation on that
 

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Tried searching around the web, got one thing to know, excel is not compitible to process this many data
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
That's a lot of formulas, but you may want to investigate further. Are you using any volatile functions as shown at this link:

Are you using conditional formatting?

Are you referencing entire columns anywhere, or are you using narrower ranges that are a closer match to the actual location of the data? For example, INDEX($B$4:$B:45000... vs. INDEX($B:$B...

Any of those can create some slowness issues with larger workbooks.

Does the problem go away if you delete the formula I suggested? If so, does the problem go away if you use the first version of the formula I suggested...the version that displays the ratio only for the "CE" entries, but not the PE entries?
 

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I am using conditional formatting
I am referencing entire columns in almost 12 rows
and formula that you have given later on, works fine for me
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,073
Office Version
  1. 2019
Platform
  1. Windows
If recalculation times are too long, you might want to investigate changing the whole-column references to something smaller. Earlier you mentioned having data extending down to about row 45000. By changing the whole-column references to 50000 or even 100000 maximum, you could significantly reduce the size of arrays and the number of computations. I hesitate to suggest changing conditional formatting because you may very well depend on that for enhanced functionality, but conditional formatting too can be responsible for slow performance. In any case, you might review the conditional formatting rules to ensure that the rule(s) is/are not being applied to cells that do not matter. In other words, conditional formatting applied to whole-column references would not be necessary.

Do any of your other formulas make use of functions: NOW, TODAY, RANDBETWEEN, OFFSET, INDIRECT, INFO, CELL, SUMIF? The first five, especially if used frequently, can cause slow performance; and if they are used, then some effort might be invested in changing the worksheet to reduce their usage.

If you want to investigate any of these things and are seeking some additional help or insights, then since the focus is changing from the original post, you might be better served to create a new request for help, describe the issues, and offer some examples of the formulas so that others will know about this unresolved issue.
 

adpatel

New Member
Joined
May 30, 2019
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
ok, thank you very much for suggestion and i will be greatful to you for your help
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top