Formula not returning correct result using MODE() function

[jackmcguigan1]

Hi all,

I have four cells which show a number (the number is 1, 2, 3 or 4). I have a cell beside those which looks at all four cells and returns the most common number (I'm using MODE() for that). All worked fine. I had an error when it showed 1,2 3 and 4 so I modified the formula that if there was an error to show the highest (MAX()) value. Agai, worked perfectly well. I now have an issue if the results are 1,1,2,2 or 2,2,3,3 etc. Any result where there are two pairs effectively. The formula won't return the highest number. Any ideas? The formula I'm using is:

=IF(ISERROR( MODE(T9:W9)),MAX(T9:W9),MODE(T9:W9))

I'm using Excel 2011.

TIA

Jack

[jackmcguigan1]

Apologies Jason, subject line now amended t reflect

[Tony Valko]

I now have an issue if the results are 1,1,2,2 or 2,2,3,3 etc. Any result where there are two pairs effectively. The formula won't return the highest number. Any ideas? The formula I'm using is:

=IF(ISERROR( MODE(T9:W9)),MAX(T9:W9),MODE(T9:W9))

That's because you have a MODE value.

1,1,2,2 or 2,2,3,3 are both multi-modal.

[jason.b75]

Try this Array formula,

=MAX(IF(MAX(COUNTIF(T9:W9,T9:W9))=COUNTIF(T9:W9,T9:W9),T9:W9))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.