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

[jackmcguigan1]

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