functions to determine max in a table

[jeffouille]

Dear All,

I have the following kind of table:

A1: Inception Date
B1: Risk profile
C1: Security Name
D1: Closing Date
E1: Performance

in column A, i have regular dates
in column B i have either "L", or "M", or "H"
in column C i have some text
in column D, i have a date or a blank
in column E i have a number (positive or negative)

1) What id like to achieve is a function somewhere that gives me the name of the security name, has the highest performance among all entries with same Risk profile (L, M or H) and blank CLosing dates.

2) Same question, but now i want the inception date, not the security name

3) i want the performance itself for that same secuirty name

I wished i was able to paste a table, many thanks for any guidanc e!!!
Jeffouille

 

[Trevor G]

You could use copy and paste as normal or use Excel Jeanie to upload sample data. It would help create a solution to see some sample data.

 

[jeffouille]

Sure, didnt know it was fine... So for only open positions (Closing date = blank) id like to fill that table

<TABLE style="WIDTH: 471pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=628 border=0 x:str><COLGROUP><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4900" width=134><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 144pt; mso-width-source: userset; mso-width-alt: 7021" width=192><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl29 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 131pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white; mso-ignore: colspan" align=left width=174 colSpan=2 height=19>Best since inception</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 144pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=192>Bond Desc</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 119pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=159>Since</TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=103>Total Return</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" align=left height=21>Low Risk Profile</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" align=left height=21>Medium Risk Profile</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" align=left height=21>High Risk Profile</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD><TD class=xl30 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">?</TD></TR></TBODY></TABLE>

Set of data is as such:

<TABLE style="WIDTH: 333pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=444 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" width=72 height=19 x:num="40599">25-Feb 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 30pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=40>M</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 119pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=159>STATSP 5 3/8 03/31/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" width=82> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" width=91 x:num="1.9737024125719239E-2">1.97%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40599">25-Feb 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">DEWAAE 7 3/8 10/21/20</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:num="40612">10-Mar 11</TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:str=""> </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40599">25-Feb 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">BNKEA 8 1/2 11/29/49</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="6.0603731037310246E-3">0.61%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40632">30-Mar 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">M</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">TEXTEX 7 5/8 01/19/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white" x:num="40778">23-Aug 11</TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:str="">7.00% </TD></TR><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: white" height=18 x:num="40668">05-May 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">M</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">CITIC 6 3/4 05/15/14</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-4.929001203369459E-3">-0.49%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">TNEFT 5.67 03/05/14</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-8.0093023255813599E-3">-0.80%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">RESOUR 3 3/4 08/03/15</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-2.8614609571789278E-3">-0.29%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">AXSBIN 5 1/4 09/30/15</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="2.0135527589545486E-3">0.20%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">WOORIB 4 3/4 01/20/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="1.0667932890155302E-3">0.11%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">RELECL 4 1/4 01/25/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="2.3686399787028696E-2">2.37%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">M</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">LONKIN 8 1/2 06/03/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-2.0219966159052392E-2">-2.02%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">M</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">MASQUH 0 01/24/17</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="2.2059349593495953E-2">2.21%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">M</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">XINAOG 6 05/13/21</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="4.1459566074950535E-2">4.15%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">PRBANK 9 3/8 09/23/15</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-2.7173913043478253E-2">-2.72%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">HIDILI 8 5/8 11/04/15</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-8.2469072164948504E-2">-8.25%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl38 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">LNGFOR 9 1/2 04/07/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-1.6456486042692904E-2">-1.65%</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">BACR 5.926 09/29/49</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-0.101521568627451">-10.15%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40708">14-Jun 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">H</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">HUWHY 6 12/29/49</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-4.8193939393940122E-3">-0.48%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40792">06-Sep 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">RSHB 7 1/8 01/14/14</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-4.5939371257485151E-3">-0.46%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40792">06-Sep 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">VTB 6.465 03/04/15</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-7.1142857142857396E-3">-0.71%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40792">06-Sep 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">NOBLSP 4 7/8 08/05/15</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-1.2553086419753057E-2">-1.26%</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: white" height=19 x:num="40792">06-Sep 11</TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">L</TD><TD class=xl27 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white">NTPCIN 5 7/8 03/02/16</TD><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"> </TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; BACKGROUND: white; BORDER-LEFT: #919191; COLOR: #4d92b4; BORDER-BOTTOM: #ece9d8; mso-ignore: style; mso-pattern: auto none; text-line-through: none" x:num="-5.597826086956581E-3">-0.56%</TD></TR></TBODY></TABLE>

for columns A,B,C,D,E

So for my first answer, id want :

Best performer (Low profile) =
RELECL 4 1/4 01/25/16
14-Jun 11
2.37%

Best performer (Medium profile) =
XINAOG 6 05/13/21
14-Jun-11
4.15%

etc...

it is important that i have a test and only check within the cells that have a blank Closing Date

 

[jeffouille]

any help anyone please ?

 

[Trevor G]

Can you explain how you get your answer for the Low Profile and what does the Bond Desc mean is that the percentage that is written in the cell?

How would you get the since date? And how would you get the total Return.

If you can explain these then you might get the answer you need.

Excel Workbook
	A	B	C	D	E
1	Best since inception		Bond Desc	Since	Total Return
2	Low Risk Profile		RELECL 4 1/4 01/25/16	14-Jun-11	2.37%
3	Medium Risk Profile		?	?	?
4	High Risk Profile		?	?	?
Sheet1

From these

Excel Workbook
	A	B	C	D	E
9	25-Feb-11	M	STATSP 5 3/8 03/31/16		1.97%
10	25-Feb-11	H	DEWAAE 7 3/8 10/21/20	10-Mar-11
11	25-Feb-11	H	BNKEA 8 1/2 11/29/49		0.61%
12	30-Mar-11	M	TEXTEX 7 5/8 01/19/16	23-Aug-11	7.00%
13	05-May-11	M	CITIC 6 3/4 05/15/14		-0.49%
14	14-Jun-11	L	TNEFT 5.67 03/05/14		-0.80%
15	14-Jun-11	L	RESOUR 3 3/4 08/03/15		-0.29%
16	14-Jun-11	L	AXSBIN 5 1/4 09/30/15		0.20%
17	14-Jun-11	L	WOORIB 4 3/4 01/20/16		0.11%
18	14-Jun-11	L	RELECL 4 1/4 01/25/16		2.37%
19	14-Jun-11	M	LONKIN 8 1/2 06/03/16		-2.02%
20	14-Jun-11	M	MASQUH 0 01/24/17		2.21%
21	14-Jun-11	M	XINAOG 6 05/13/21		4.15%
22	14-Jun-11	H	PRBANK 9 3/8 09/23/15		-2.72%
23	14-Jun-11	H	HIDILI 8 5/8 11/04/15		-8.25%
24	14-Jun-11	H	LNGFOR 9 1/2 04/07/16		-1.65%
25	14-Jun-11	H	BACR 5.926 09/29/49		-10.15%
26	14-Jun-11	H	HUWHY 6 12/29/49		-0.48%
27	06-Sep-11	L	RSHB 7 1/8 01/14/14		-0.46%
28	06-Sep-11	L	VTB 6.465 03/04/15		-0.71%
29	06-Sep-11	L	NOBLSP 4 7/8 08/05/15		-1.26%
30	06-Sep-11	L	NTPCIN 5 7/8 03/02/16		-0.56%
Sheet1