• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNTIF or SUMPRODUCT Question

B

bonds

New Member
Hi everyone.


I'm having trouble filtering some data that I believe requires the use of the COUNTIF or SUMPRODUCT formulas. I've gotten close using variations of the two, but need the help of this great community!


I have a large spreadsheet of bond data that contains two columns displaying bond ratings from two ratings agencies. I would like to use a formula to identify all of the bonds in the table that are below investment grade.


The first column with ratings by Moody's can contain the following fields: Aaa, Aa1, Aa2, Aa3, A1, A2, A3, Baa1, Baa2, Baa3, Ba1, Ba2, Ba3, B1, B2, B3, Caa1, Caa2, Caa3, Ca, C. For Moody's ratings, all bonds Ba1 and lower are considered below investment grade.


The second column with ratings by S&P can contain the following fields: AAA, AA+, AA, AA-, A+, A, A-, BBB+, BBB, BBB-, BB+, BB, BB-, B+, B, B-, CCC+, CCC, CCC-, CC, C, D. For S&P ratings, all bonds BB+ and lower are considered below investment grade.


I would like to use a formula to look at these two columns in the table, and count all occurances of a bond that contains at least one of these below investment grade ratings (but I want to make sure I don't double count a row if both columns contain a below investment grade rating, that should only count once).


I would greatly appreciate your help in explaining how best to approach this problem.


Thanks in advance!
 
Bonds


Firstly, Welcome to the Chandoo.org Forums


Why not just select the whole data area

Then make a Pivot table out of it

use the Moodys as the Column and S&P as the Rows

Then Count on any field
 
Hi ,


The problem appears to be how to equate the ratings with rankings ; the ratings do not follow normal alphabetical order ; you might have to introduce a lookup table , and add a column or two columns to your data to convert the textual ratings to numeric rankings. Is this possible ?


Narayan
 
Thank you Hui and Narayank991.


I attempted a Pivot Table as well Hui, but I'm not sure how to identify duplicates when doing so. I want to count all bonds that are below investment grade, but if the same row of data contains a below investment grade rating for column 1 and column 2, I don't want that to be counted twice.


For example (please excuse the poor formatting):


CUSIP | Bond Data 1| More Bond Data| Rating 1 | Rating 2

123XYZ | Municipal | Revenue Bond | Caa1 | CCC+


That entry should only count as 1 below investment grade bond even though both rating agencies gave it a below investment grade rating.
 
Hi Bonds,


Please see this:


http://dl.dropbox.com/u/60644346/Moody%26SPRatting.xlsx


Regards,
 
Faseeh, that is great, thank you.


It is unfortunately still just a small step away from what I need though.


The problem is that one rating agency may rate it investment grade, while the other may rate it below investment grade. On the otherhand, BOTH agencies may rate it below investment grade. If that is the case, using the table you've provided (which is great, thank you), it would unfortunately count that single bond as 2 occurences of a below investment grade bond.


Using your approach, is there a way to only count that scenario as 1 instance of a below investment grade bond rather than 2?
 
Hello @bonds,

Here is one approach...


For clarity, I have named the low investment grade ratings for Moody's and S&P as:

Moody_LowRating and SP_LowRating, respectively.


Then you could use the following formula to obtain a count:

=SUMPRODUCT(SIGN(ISNUMBER(MATCH(B2:B12, Moody_LowRating,0)) + ISNUMBER(MATCH(C2:C12, SP_LowRating,0))))


In this case, B2:B12 has the Moody's rating for a given bond, and C2:C12 has the S&P rating for the same bond.


Cheers,

Sajan.
 
Hello again everyone.


I hate to reopen an old thread like this, but I need your guidance here yet again.


I had my original question answered by Sajan's great formula: =SUMPRODUCT(SIGN(ISNUMBER(MATCH(B2:B12, Moody_LowRating,0)) + ISNUMBER(MATCH(C2:C12, SP_LowRating,0))))


I have a slight addition to make to my original problem though. Is it easy to work an If statement into this formula? I have a column in my data table that displays certain bond types (corporates, municipals, treasuries, etc...). I only want Sajan's formula to run on those rows where the bond type = "Corporate".


So lets say the bond "Type" column is column A. Is there a quick addition I can make to Sajan's formula that will only return results for those rows that the type = "Corporate"?


Thank you again to the community for your guidance.
 
Hi,

You should be able to add the condition just by extending the SUMPRODUCT formula as follows:

=SUMPRODUCT((E2:E12="Corporate")*SIGN(ISNUMBER(MATCH(B2:B12, Moody_LowRating,0)) + ISNUMBER(MATCH(C2:C12, SP_LowRating,0))))


where E2:E12 contains the bond-type.


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top