Choosing highest ranked value in a row text and numbers

Hi,

I am trying to get a cell let's say A4 to tell me the highest ranked value in that row A5 to A15, the row consists of numbers and letters, so it could have A, B, C, 0, 1, 3, 2. In this case the answer would be 3, but if there was A, B, C, 1, 2, CXL then CXL would be the answer.

The only way I can think of doing this is creating a table with the rankings in order as a reference table so cell A4 looks at the row and compares this with the table. I don't know what the formula would be though.

The formula has to be able to be copied down, and if the reference table is the way to go I would like the table to be on sheet 2

Thanks

Perfect, thank you so much!

Hey,

I hit another problem, when i put this into practice it came up with a error and put NAME## in the cell, i have attached an example of the sheet I'm using, P1 has the lowest rank, then all the letters, then numbers and CANCELLED has the highest rank. Cell C8 is where the answer needs to be, everything right of that in the same row is data for the rank.

Same as before where this can be copied down.

Thanks in advance

Yeh this works now, thanks very much!

Hey,

Thanks for helping yesterday, i have one final question which may seem silly, how will i copy this to similar spreadsheets, I am copying the formula into another excel document in the same cell, I also copied the table on sheet 2 into the document into the same location but the result seems to be ####.

If there is something else I am suppose to copy apart from the table on sheet 2 and the formula then that could be the problem.

The spreadsheet I am copying it to is the same as I sent you just with confidential info placed in, but the numbers and letters (i.e A, 0, P1) are the same as the one i sent you are in the same places so i don't know why it isn't copying.

Thanks in advance

Thanks, this works perfectly!