Creating Multi Condition Flags

**bdparks** · 07-12-2013, 11:39 AM

Hello -

My problem is the following:

I have a list of bonds for three different companies.

I need to write a formula which identifies my "benchmark" 10year bond for each company.

For a bond to be considered my benchmark, it needs to meet the following conditions:
1) Crncy = USD
2) Amt Outstanding (USD) > 100,000,000
3) maturity of the bond is closest to 10 years as compared to all other USD denominated bonds of the same company with more than 100,000,000 Amt Outstanding (USD)

This needs to be accomplished formuaically (rather than a pivot) and the result of the formula needs to drop a flag into column D which can then be used for other lookups.

Thank you very much!

**Ace_XL** · 07-12-2013, 09:07 PM

Try this array formula..

=I16=MIN(IF(($J$16:$J$103="USD")*($M$16:$M$103>=100000000)*($C$16:$C$103=C16)*($I$16:$I$103>10),$I$16:$I$103))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

TRUE indicates the flag

**bdparks** · 07-12-2013, 09:56 PM

Thanks Ace for the reply!

I tried out the formula. It has two issues that I can see:

1)the only true result returned was in row 44 for the bond maturing March 1, 2026. The result the formula should return is in row 40 for the bond maturing February 24, 2022. This bond meets all of the conditions better than the March 1, 2026 bond as it's time to maturity is closer to 10 years.

2) the formula, once copied down, only returns one result.

I'd sincerely appreciate you taking another look.

Thanks again.