I'm working on a document to see from historic data the occurrence of fallen angels/rising stars among a data set. I've pulled through all the ratings over time, companies, etc. What I am having issue with is evaluating, over a yearly period, the ratings.
The formula I am using is below
=IF(AND(Ratings!C30={"BB+","BB","BB-","B+","CCC+","CCC","CCC-","CC","C","D"},Ratings!BK30={"AAA","AA+","AA","AA-","A+","A","A-","BBB+","BBB","BBB-"}),"Y","N")
Ratings! C30 is a month in a year (let's says 2020) and BK30 is the same month a few years afterwards. I am trying to find out where non-investment grade ratings have changed to investment grade. With the above I always get "N" even though C30 is bb+ and BK30 is a-. I can't figure out where I'm going wrong. I've tried breaking the formula into smaller chunks and they work individually, but once I combine them it just fails.
How can I fix this?
Thanks!
Your formula cannot ever be true since C30 cannot be all those items at once and nor can BK30. I think you wanted:
=IF(AND(OR(Ratings!C30={"BB+","BB","BB-","B+","CCC+","CCC","CCC-","CC","C","D"}),OR(Ratings!BK30={"AAA","AA+","AA","AA-","A+","A","A-","BBB+","BBB","BBB-"})),"Y","N")