In excel, how to find largest letter in a range which also contain some blank cells

0
votes

In my excel sheet, I wanted to find the largest letter in a range. Some cells in the range are blank cells.

I have tried using a code obtained from a website, however it works only when there is no blank cell.

LOOKUP(2,1/(COUNTIF(D6:L6,">"&D6:L6)=0),D6:L6)

No output when some cells in the range are blank cells, i mean not all cells contain letter.

Sample data :

result is "B" 

B   B   E   B   B   B   B   C

result in nothing (because not all 8 cells contain letter, 2 cells are blank). 

B       E       C   C   B   C
1
excel-formula
Can you add some sample data and the expected result? – BigBen
I've edited the question. Thanks but sorry i dont know how to upload a spreadsheet data here – user4180952
If there are "A", "B", "C" then the largest letter is "A" – user4180952
Funny, I would have thought C would be the largest letter. good thing you clarified that. – Forward Ed
I only use capitall letter. The solution work like charm. Thank you so much. – user4180952

1 Answers

1
votes

Depending on your data, you may be able to do something like this, using CHAR, CODE, and AGGREGATE to ignore blank cells:

=CHAR(AGGREGATE(15,6,CODE(A1:A10),1))

enter image description here