Finding the Maximum in an Ordinal Data Set

[bbllueataznee]

I have a very long list of companies and their credit ratings.
I would like to find the maximum, minimum and median credit rating in that list (I would like to return the credit rating, not the company).

I am using S&P ratings and pooling lower scores. My categories are (ranked) AAA, AA+, AA, AA-, A+, A, A-, BBB+, BBB, BBB-, NR (not rated).

What functions should I use?
1. I would prefer not to use VBA so that I can explain it to my boss.
2. I am happy to create a reference table elsewhere in the spreadsheet than ranks the categories if this will help.

Thanks guys

[martindwilson]

first id give each a numerical value
=LOOKUP(A1,{"A","A-","A+","AA","AA-","AA+","AAA","BBB","BBB-","BBB+","NR"},{6,7,5,3,4,2,1,9,10,8,11})
you can then do whatever you like with the values

[bbllueataznee]

Much appreciated martindwilson.
I ended up using the following (example) formulae to produce single cell answers:

MAXIMUM
= LOOKUP(MAX(IFERROR(LOOKUP(
$A$1:$A$10,
{"A","A-","A+","AA","AA-","AA+","AAA","BBB","BBB-","BBB+","NR"},
{5,4,6,8,7,9,10,2,1,3,"NR"}),"NR")),
{1,2,3,4,5,6,7,8,9,10},
{"BBB-","BBB","BBB+","A-","A","A+","AA-","AA","AA+","AAA"})

MINIMUM
= LOOKUP(MIN(IFERROR(LOOKUP(
$A$1:$A$10,
{"A","A-","A+","AA","AA-","AA+","AAA","BBB","BBB-","BBB+","NR"},
{5,4,6,8,7,9,10,2,1,3,"NR"}),"NR")),
{1,2,3,4,5,6,7,8,9,10},
{"BBB-","BBB","BBB+","A-","A","A+","AA-","AA","AA+","AAA"})

The only thing that was frustrating was realising that the "lookup_vector" in the "lookup()" function must be in alphabetic and numerical order. I didn't know this as I usually use vlookup and I don't think that vlookup will take a vector in the "lookup_value" input field.
Any simplifications to my formulae would be welcome (other than reverting to cell referencing the vectors).

[martindwilson]

you can use a table instead lookup(a1,j1:j11,h1:h11) or if you dont want to sort
=index(h1:h11,match(a1,j1:j11,0)) where h contains text j contains your numbers

[bbllueataznee]

Thanks but as I said, I did use a table ("cell referencing the vectors") and whilst this does reduce the size of my formula, it also serves to increase the number of cells used to perform this calc.

As for the indexing solution, would work well but I ideally wanted to input a vector of credit ratings where you have denoted "a1" and match() does not support vector input in the "lookup_value" input field as far as I can tell. I would still need the iferror() function as well as blanks produce na() which the max() function does not play well with.