Multiplication Problem ...Suggestion required

[zaska]

Hi,


<table border="0" cellpadding="0" cellspacing="0" width="554"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL</td> <td class="xl65" style="border-left:none;width:60pt" width="80">TIMESTAMP</td> <td class="xl65" style="border-left:none;width:48pt" width="64">OPEN</td> <td class="xl65" style="border-left:none;width:48pt" width="64">HIGH</td> <td class="xl65" style="border-left:none;width:48pt" width="64">LOW</td> <td class="xl65" style="border-left:none;width:71pt" width="95">CONTRACTS</td> <td class="xl65" style="border-left:none;width:66pt" width="88">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-I</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">11299.9</td> <td class="xl65" style="border-top:none;border-left:none">11346.95</td> <td class="xl65" style="border-top:none;border-left:none">11212.25</td> <td class="xl65" style="border-top:none;border-left:none">39508</td> <td class="xl65" style="border-top:none;border-left:none">969625</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-II</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">11340.05</td> <td class="xl65" style="border-top:none;border-left:none">11354.75</td> <td class="xl65" style="border-top:none;border-left:none">11240</td> <td class="xl65" style="border-top:none;border-left:none">640</td> <td class="xl65" style="border-top:none;border-left:none">22625</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">BANKNIFTY-III</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-I</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">6668.5</td> <td class="xl65" style="border-top:none;border-left:none">6729.95</td> <td class="xl65" style="border-top:none;border-left:none">6660</td> <td class="xl65" style="border-top:none;border-left:none">81</td> <td class="xl65" style="border-top:none;border-left:none">15650</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-II</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">50</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;border-top:none" height="20">CNXIT-III</td> <td class="xl66" style="border-top:none;border-left:none">20110701</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> <td class="xl65" style="border-top:none;border-left:none">0</td> </tr> </tbody></table>
The above table is the original data. I have one more excel file stored in E Drive which contains the Lot Size values for each symbol but there is only one symbol reference for all the Three expiry dates because the lot size is same for Each contract be it First Month Contract (-I),Second Month Contract (-II) or Third Month Contract (-III)

Example of Lot size data

<table border="0" cellpadding="0" cellspacing="0" width="163"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL </td> <td class="xl65" style="width:48pt" align="right" width="64">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY </td> <td align="right">25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY </td> <td align="right">20</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50</td> <td align="right">150</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Symbol </td> <td class="xl65" align="right">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">3IINFOTECH</td> <td align="right">8000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">TRIVENI </td> <td align="right">2000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABAN </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABB </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABGSHIP </td> <td align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABIRLANUVO</td> <td align="right">500</td> </tr> </tbody></table>
Example of desired output in the original table

BANKNIFTY-I 39508*25
BANKNIFTY-II 640 *25
BANKNIFTY-III 0 *25
CNXIT-I 81 *50

Etc..

I want to multiply the Values of Contract Column in the orignial table with the Lot size values Referred to in another excelsheet.

Kindly give your suggestions

Regards,
Zaska

 

[Aladin Akyurek]

Yes sir in .csv format. I copied the data from Lot size into Sheet2 of Data

Both the files are in .Csv format.

Regards,

Zaska

You probably need to clean up the data. What does the following...

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$13,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),Sheet2!$B$1:$B$13)

turn up?

 

[zaska]

Sir,

Kindly tell me what should be done to clean up the data. I couldn't notice any difference between the two. There are no spaces.

The Formula is still returning errors because of data

Thank you

 

[zaska]

sir,

I found the solution.

Can you kindly explain what actually the formula did? I didn't understand the formula.


The 999999999 + 307 is just like a puzzle for me.

Thank you

 

[Aladin Akyurek]

sir,

I found the solution.

And that is?

Can you kindly explain what actually the formula did? I didn't understand the formula.


The 999999999 + 307 is just like a puzzle for me.

Thank you

It's 9.99999999999999E+307, the largest allowed positive number in Excel. This number is used as a look up value in an idiom that picks out the last numeric value from a reference:

http://www.mrexcel.com/forum/showthread.php?t=102091

http://www.mrexcel.com/forum/showthread.php?t=310278

 

[zaska]

Sir,

I changed A13 to A226 and B13 to B226

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),Sheet2!$B$1:$B$226)

This solved my problem..Though i didn't understand the whole formula.

Thank you

 

[Aladin Akyurek]

Sir,

I changed A13 to A226 and B13 to B226

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$226,
"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),Sheet2!$B$1:$B$226)

This solved my problem..Though i didn't understand the whole formula.

Thank you

Ok...

TRIM removes extranous spaces from an entry.

SUBSTITUTE removes the character instances whose code is 160 from an entry. Web pages often contain them.

The main formula expression, put more concisely...

LOOKUP(BigNum,SEARCH(List1,A2),List2)

has the following parts...

SEARCH seeks every item from List1 in A2 and returns the start position (an integer) of every item it succeeds to find in A2 and a #VALUE error it fails. So we get an array:

{#VALUE!,#VALUE!,7,#VALUE!,...}

LOOKUP (with BigNum as look up value) picks out 7 if there is no other
numeric value from that array. Note that 7 occurs as the 3rd item in this
array. This means that the 3rd item from List1 is found in A2. If you feed
LOOKUP an assciated list like List2, this function will return the 3rd item
as result. You have text values in List1 and corresponding numbers in List2.

Why "#"& as prefix for List1 and A2? Well, we need to distinguish
the occurrences of:

AKBANK
BANK

in an entry like:

AKBANK-III

We want AKBANK to be found, not BANK. If you omit "#", SEARCH would found both and LOOKUP would pick out the last numeric value corresponding
to BANK as we would get:

{...,1,3,...}

Looking for:

#AKBANK
#BANK

in

#AKBANK-III

allows us to avoid such an unintended outcome.

 

[zaska]

Sir,

What Should be the formula if the Lot Size Values are housed in Sheet1 of NSE Converter.xls ( another workbook) which is opened. The NSE Converter.xls is stored in E:\Macros


=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$226, "#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),Sheet2!$B$1:$B$226)

Thanks for the explanation.

Regards.

Zaska

 

[Aladin Akyurek]

Sir,

What Should be the formula if the Lot Size Values are housed in Sheet1 of NSE Converter.xls ( another workbook) which is opened. The NSE Converter.xls is stored in E:\Macros


=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&Sheet2!$A$1:$A$226, "#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),Sheet2!$B$1:$B$226)

Thanks for the explanation.

Regards.

Zaska

Open NSE Converter.xls.
Go the formula sheet.
Select the formula cell.
Go to the Formula Bar.
Select the Sheet2!$A$1:$A$226 bit.
Go to NSE Converter.xls and select the relevant the range.
Adjust or do the same with the Sheet2!$B$1:$B$226 bit.

 

[zaska]

Sir,

The following changes were made but i got message that the formula contained error

=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&[E:\Macros\NSE Converter.xls]Sheet1!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),[E:\Macros\NSE Converter.xls]Sheet1!$B$1:$B$226)"

Thank u

 

[Aladin Akyurek]

Sir,

The following changes were made but i got message that the formula contained error

=F2*LOOKUP(9.99999999999999E+307,
SEARCH(""#""&[E:\Macros\NSE Converter.xls]Sheet1!$A$1:$A$226,
""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),
[E:\Macros\NSE Converter.xls]Sheet1!$B$1:$B$226)"

Thank u

Should be rather something like:

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[E:\Macros\NSE Converter.xls]Sheet1'!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[E:\Macros\NSE Converter.xls]Sheet1'!$B$1:$B$226)