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.

 

[adpatel]

ok, let me give a try

 

[adpatel]

Not speeding up!

 

[KRice]

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]

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]

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

 

[KRice]

That's a lot of formulas, but you may want to investigate further. Are you using any volatile functions as shown at this link:

Excel Recalculation

Office developer client VBA reference documentation

docs.microsoft.com

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]

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]

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]

ok, thank you very much for suggestion and i will be greatful to you for your help