Help needed finding the most popular letter in a row of letters

[MiguelWallis]

Hi,

I have an error in the following formula:

={CHAR(MODE(CODE("M2:"&ADDRESS(ROW(M2),COLUMN(AF2)-COUNTBLANK(M2:AF2),4))))}

The problem I'm trying to address is finding the most popular letter in a row of letters. Each cell in the row contains only one letter, but in some cases the letter might be blank. The formula above is an attempt to address this by finding the last non-blank value, as the CODE function accepts values between 1 and 255 and doesn't appear to handle blank values.

The formula is also an array formula (entered by pressing CTRL-SHIFT-ENTER); if I replace the part within the CODE element with a cell range reference, the formula appears to work. However, as the length of the word may change, I need some way of adjusting which cells are referenced.

Hopefully someone can help with this? If you need any further information let me know.

Thanks,

Miguel

 

[mikerickson]

How about the CSE formula
=CHAR(MODE(IF(CODE(M2:IV2&" ")<>32,CODE(M2:IV2))))

This formula needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[MiguelWallis]

Fantastic! It looks a lot more elegant than my original sprawling, clumsy attempt as well...

I'm curious as to what the structure and syntax is for this, because I've been struggling to explain what each step of the formula was doing. I think I've finally figured out the nested IF statement:

[...] IF(CODE(M2:IV2&" ")<>32 [...]

I assume that 32 is the CODE result of a space? So am I right in thinking that this IF statement adds a space to whatever is in the cells between the range M2 and IV2. So if the cell is blank, the result is just a space -- which is what the IF statement is catching.

Seems like a very imaginative way of solving the problem! Thanks again

 

[wigi]

Yes, you're totally right Miguel.