Formulas within Array Constants (INDEX MATCH within SMALL)

[gcefaloni]

Hi,

I have the following Credit Rating Table

#	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
100​	AAA	AAA	Aaa	AAA
95​	AA+	AAH	Aa1	AA+
90​	AA	AA	Aa2	AA
85​	AA-	AAL	Aa3	AA-
80​	A+	AH	A1	A+
75​	A	A	A2	A
70​	A-	AL	A3	A-
65​	BBB+	BBBH	Baa1	BBB+
60​	BBB	BBB	Baa2	BBB
55​	BBB-	BBBL	Baa3	BBB-
50​	BB+	BBH	Ba1	BB+
45​	BB	BB	Ba2	BB
40​	BB-	BBL	Ba3	BB-
35​	B+	BH	B1	B+
30​	B	B	B2	B
25​	B-	BL	B3	B-
20​	CCC+	CCCH	Caa1	CCC
15​	CCC	CCC	Caa2	CCC
10​	CCC-	CCCL	Caa3	CCC
5​	CC	CC	Ca	DDD
0​	D	D	C	DD

I'm trying to get this formula working:

=SMALL(IFERROR(M9:P9,""),ROW(1:1))

Where currently: M9 is =INDEX(Credit_Ratings,MATCH(M6,Table1[[#All],[RTG_SP]],0),MATCH("#",Table1[#Headers],0))
N9=INDEX(Credit_Ratings,MATCH(N6,Table1[[#All],[RTG_DBRS]],0),MATCH("#",Table1[#Headers],0))
O9=INDEX(Credit_Ratings,MATCH(O6,Table1[[#All],[RTG_MOODY]],0),MATCH("#",Table1[#Headers],0))
P9=INDEX(Credit_Ratings,MATCH(P6,Table1[[#All],[RTG_FITCH]],0),MATCH("#",Table1[#Headers],0))

But I do not want to have a reference to the M9:P9 cells or any other cells for a matter of fact.. So I would like to do something that incorporates the INDEX MATCH formulas within the initial SMALL formula so that I can know which is the smallest #s just from referring the letters.

Could I do something like the following?
{=SMALL(IFERROR({(INDEX(Credit_Ratings,MATCH(M6,Table1[[#All],[RTG_SP]],0),MATCH("#",Table1[#Headers],0))),(INDEX(Credit_Ratings,MATCH(N6,Table1[[#All],[RTG_DBRS]],0),MATCH("#",Table1[#Headers],0))),(INDEX(Credit_Ratings,MATCH(O6,Table1[[#All],[RTG_MOODY]],0),MATCH("#",Table1[#Headers],0))),(INDEX(Credit_Ratings,MATCH(P6,Table1[[#All],[RTG_FITCH]],0),MATCH("#",Table1[#Headers],0)))},""),ROW(1:1))}

Right now, when I'm trying this, it is giving me the "There's a problem with this formula." popup.

Thanks a lot for your help!

Best regard,

Gabriel

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E							L	M	N	O	P	Q	R
1	Score	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
2	100	AAA	AAA	Aaa	AAA
3	95	AA+	AAH	Aa1	AA+
4	90	AA	AA	Aa2	AA
5	85	AA-	AAL	Aa3	AA-								RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH		Scores in ascending order
6	80	A+	AH	A1	A+								A+	A	Aa3	BBB		60
7	75	A	A	A2	A													75
8	70	A-	AL	A3	A-													80
9	65	BBB+	BBBH	Baa1	BBB+													85
10	60	BBB	BBB	Baa2	BBB
11	55	BBB-	BBBL	Baa3	BBB-
12	50	BB+	BBH	Ba1	BB+
13	45	BB	BB	Ba2	BB
14	40	BB-	BBL	Ba3	BB-
15	35	B+	BH	B1	B+
16	30	B	B	B2	B
17	25	B-	BL	B3	B-
18	20	CCC+	CCCH	Caa1	CCC
19	15	CCC	CCC	Caa2	CCC
20	10	CCC-	CCCL	Caa3	CCC
21	5	CC	CC	Ca	DDD
22	0	D	D	C	DD
Sheet7

Cell Formulas
Range	Formula
R6:R9	R6	=SMALL(SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M$6:P$6),ROW(1:1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[gcefaloni]

Maybe:

Book1
	A	B	C	D	E							L	M	N	O	P	Q	R
1	Score	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
2	100	AAA	AAA	Aaa	AAA
3	95	AA+	AAH	Aa1	AA+
4	90	AA	AA	Aa2	AA
5	85	AA-	AAL	Aa3	AA-								RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH		Scores in ascending order
6	80	A+	AH	A1	A+								A+	A	Aa3	BBB		60
7	75	A	A	A2	A													75
8	70	A-	AL	A3	A-													80
9	65	BBB+	BBBH	Baa1	BBB+													85
10	60	BBB	BBB	Baa2	BBB
11	55	BBB-	BBBL	Baa3	BBB-
12	50	BB+	BBH	Ba1	BB+
13	45	BB	BB	Ba2	BB
14	40	BB-	BBL	Ba3	BB-
15	35	B+	BH	B1	B+
16	30	B	B	B2	B
17	25	B-	BL	B3	B-
18	20	CCC+	CCCH	Caa1	CCC
19	15	CCC	CCC	Caa2	CCC
20	10	CCC-	CCCL	Caa3	CCC
21	5	CC	CC	Ca	DDD
22	0	D	D	C	DD
Sheet7

Cell Formulas
Range	Formula
R6:R9	R6	=SMALL(SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M$6:P$6),ROW(1:1))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Thank you Eric.

However, I'm getting a small error that I can't fix that I forgot to mention in my initial post. It is possible that some of the credit agencies don't give a rating to a stock and therefore instead of the table values shown, it returns either a "NR" or "#N/A #N/A" or "#N/A Invalid Security". Right now, those error messages return a 0 and therefore it skews the rating.

This is what I'm trying to recreate:
"• If only one agency rates a security, use that rating;
• If two agencies rate a security, use the lower of the two ratings;
• If three agencies rate a security, use the middle of the three ratings;
• If four agencies rate a security, use the middle of the three lowest ratings"

Right now, this is the formula that I have, but it doesn't work perfectly when those "#N/A #N/A" come up because those return a 0 rather than a blank and therefore skew the "Lowest three or Lowest one" due to the 0 being accounted for:

{=INDEX(Credit_Ratings,MATCH(MIN((IF(COUNTIF(M6:P6,"#N/A*")=3,SMALL(SUMIFS(Credit_Ratings,OFFSET(Credit_Ratings,0,{1,2,3,4}),M$6:P$6),ROW(1:1)),999)),IF(COUNTIF(M6:P6,"#N/A*")=2,SMALL(SUMIFS(Credit_Ratings,OFFSET(Credit_Ratings,0,{1,2,3,4}),M$6:P$6),ROW(1:1)),999),(IF(COUNTIF(M6:P6,"#N/A*")=1,MEDIAN(SMALL(SUMIFS(Credit_Ratings,OFFSET(Credit_Ratings,0,{1,2,3,4}),M$6:P$6),ROW(1:3))),999)),IF(COUNTIF(M6:P6,"#N/A*")=0,MEDIAN(SMALL(SUMIFS(Credit_Ratings,OFFSET(Credit_Ratings,0,{1,2,3,4}),M$6:P$6),ROW(1:3))),999)),Table1[[#All],['#]],0),MATCH("RTG_SP",Table1[#Headers],0))}

 

[Eric W]

OK, try this:

Book1
	A	B	C	D	E							L	M	N	O	P	Q	R
1	Score	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
2	100	AAA	AAA	Aaa	AAA
3	95	AA+	AAH	Aa1	AA+
4	90	AA	AA	Aa2	AA
5	85	AA-	AAL	Aa3	AA-								RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH		Composite Score
6	80	A+	AH	A1	A+								#N/A Invalid Security	AAL	A3	A		75
7	75	A	A	A2	A
8	70	A-	AL	A3	A-
9	65	BBB+	BBBH	Baa1	BBB+
10	60	BBB	BBB	Baa2	BBB
11	55	BBB-	BBBL	Baa3	BBB-
12	50	BB+	BBH	Ba1	BB+
13	45	BB	BB	Ba2	BB
14	40	BB-	BBL	Ba3	BB-
15	35	B+	BH	B1	B+
16	30	B	B	B2	B
17	25	B-	BL	B3	B-
18	20	CCC+	CCCH	Caa1	CCC
19	15	CCC	CCC	Caa2	CCC
20	10	CCC-	CCCL	Caa3	CCC
21	5	CC	CC	Ca	DDD
22	0	D	D	C	DD
Sheet7

Cell Formulas
Range	Formula
R6	R6	=AGGREGATE(15,6,SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6)/ISERROR(SEARCH("N",M6:P6)),1+(COUNTIF(M6:P6,"*N*")<2))

I assume that any value in M6:P6 that has an "N" in it is a non-value. Be sure not to use #N/A by itself because Excel interprets that as an error and will skew the results. Empty or invalid codes in M6:P6 will also skew the results.

 

[gcefaloni]

OK, try this:

Book1
	A	B	C	D	E							L	M	N	O	P	Q	R
1	Score	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
2	100	AAA	AAA	Aaa	AAA
3	95	AA+	AAH	Aa1	AA+
4	90	AA	AA	Aa2	AA
5	85	AA-	AAL	Aa3	AA-								RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH		Composite Score
6	80	A+	AH	A1	A+								#N/A Invalid Security	AAL	A3	A		75
7	75	A	A	A2	A
8	70	A-	AL	A3	A-
9	65	BBB+	BBBH	Baa1	BBB+
10	60	BBB	BBB	Baa2	BBB
11	55	BBB-	BBBL	Baa3	BBB-
12	50	BB+	BBH	Ba1	BB+
13	45	BB	BB	Ba2	BB
14	40	BB-	BBL	Ba3	BB-
15	35	B+	BH	B1	B+
16	30	B	B	B2	B
17	25	B-	BL	B3	B-
18	20	CCC+	CCCH	Caa1	CCC
19	15	CCC	CCC	Caa2	CCC
20	10	CCC-	CCCL	Caa3	CCC
21	5	CC	CC	Ca	DDD
22	0	D	D	C	DD
Sheet7

Cell Formulas
Range	Formula
R6	R6	=AGGREGATE(15,6,SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6)/ISERROR(SEARCH("N",M6:P6)),1+(COUNTIF(M6:P6,"*N*")<2))

I assume that any value in M6:P6 that has an "N" in it is a non-value. Be sure not to use #N/A by itself because Excel interprets that as an error and will skew the results. Empty or invalid codes in M6:P6 will also skew the results.

Sir, I salute you and thank you SO MUCH! I think this works! I've been scratching my head for days on how to do this. I'm not familiar with the aggregate function but it clearly works. I'll have to do my homework on that. Also, if I want to ignore all ratings that include a "N" like you did but also any that include a "W". Could I replace the Countif by countifs and do multiple criterias? What about the search function, how should I adjust that part?

Thank you again so much!

 

[Eric W]

Well, believe it or not, checking for an N or a W really ramps up the difficulty level. Consider:

Book1
	A	B	C	D	E							L	M	N	O	P	Q	R	S
1	Score	RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH
2	100	AAA	AAA	Aaa	AAA
3	95	AA+	AAH	Aa1	AA+
4	90	AA	AA	Aa2	AA
5	85	AA-	AAL	Aa3	AA-								RTG_SP	RTG_DBRS	RTG_MOODY	RTG_FITCH		Composite Score
6	80	A+	AH	A1	A+								A+	asdf	B3	BBB+		25	65
7	75	A	A	A2	A
8	70	A-	AL	A3	A-
9	65	BBB+	BBBH	Baa1	BBB+
10	60	BBB	BBB	Baa2	BBB
11	55	BBB-	BBBL	Baa3	BBB-
12	50	BB+	BBH	Ba1	BB+
13	45	BB	BB	Ba2	BB
14	40	BB-	BBL	Ba3	BB-
15	35	B+	BH	B1	B+
16	30	B	B	B2	B
17	25	B-	BL	B3	B-
18	20	CCC+	CCCH	Caa1	CCC
19	15	CCC	CCC	Caa2	CCC
20	10	CCC-	CCCL	Caa3	CCC
21	5	CC	CC	Ca	DDD
22	0	D	D	C	DD
Sheet7

Cell Formulas
Range	Formula
R6	R6	=AGGREGATE(15,6,SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6)/(MMULT({1,1},--ISNUMBER(SEARCH({"N";"W"},M6:P6)))=0),1+(SUM(SIGN(MMULT({1,1},--ISNUMBER(SEARCH({"N";"W"},M6:P6)))))<2))
S6	S6	=AGGREGATE(15,6,SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6)/(COUNTIF(OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6)>0),1+(SUM(COUNTIF(OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6))>2))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

The formula in R6 is my first shot at it, and it works fine, but it's long and complicated. I had to use MMULT to account for some cases where there might be an N and a W in the same cell. My next idea to simplify it a bit was to ask if there is a limited number of values like "NR", "#N/A Invalid Security", "W code" that we could put in a table and use MATCH with. Then I had another idea where instead of checking for values to ignore, I check to see if the values in M6:P6 actually exist in the Credit_Ratings table. That turned out to be slightly longer, but I think it works better. I'll have a think and maybe I can improve on it a bit.

 

[Eric W]

If you change the A22 value to 1 instead of 0, this is equivalent to the S6 formula and a bit shorter:

=AGGREGATE(15,6,1/(1/SUMIFS(Credit_Ratings[Score],OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6)),1+(SUM(COUNTIF(OFFSET(Credit_Ratings[Score],0,{1,2,3,4}),M6:P6))>2))

also with Control+Shift+Enter.