Trying to get the Mode, or Average if there's no MODE in a formula

Hi all,

I've got four cells (A1:D1) where the answer can only 1,2,3 or 4. I have a summary cell (E1) which looks at all four cells and displays an answer. I want it to look at the four cells and tell me the most common number (MODE). If there is no clear MODE (2,2,3,3 or 1,2,3,4) I want it to give me an average number instead. I can get it to do one or the other but struggling to get it to work together.

I realise that 2,2,3,3 represents a Multi Mode. That is a possible answer so I need to have that in consideration. If th answer is 1,2,3,4 that presents a N/A error which I can get to work but 2,2,3,3 doesn't show an error, it only returns 2.

Any help would be very much appreciated.

TiA

Jack

This solution uses four helper cells which could be placed anywhere in row one and could also be hidden for aesthetic purposes. Lets say that you want F1:I1 to be the helper cells. Paste the following formula in F1 and drag across to I1: =COUNTIFS($A1:$D1,A1) The formula that populates the cell which gives the mode or average would then reference the helper cells, like so:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

Take a look at the attached file.
Let me know if you have any questions.

How about ...

=CHOOSE(COUNT(MODE.MULT(A1:D1)) + 1, AVERAGE(A1:D1), MODE(A1:D1), AVERAGE(A1:D1))

Or ...

=IF(MOD(COUNT(MODE.MULT(A1:D1)), 2) = 0, AVERAGE(A1:D1), MODE(A1:D1))

You're welcome.