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

##### New Member
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,

#### Attachments

• Untitled.png
25.2 KB · Views: 7

##### New Member
ok, let me give a try

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

Not speeding up!

#### KRice

##### Well-known Member
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.

##### New Member
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

##### New Member

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

#### KRice

##### Well-known Member
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?

##### New Member

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

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

Replies
3
Views
89
Replies
2
Views
928
Replies
1
Views
68
Replies
3
Views
328
Replies
19
Views
565

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.

### Which adblocker are you using?

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

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