Determine most common text in an incomplete list

[Jeff_Ho]

Hi All,

I am looking to determine the most common occurrence of a text entry in a list. I am using the following formula:

{=INDEX(E4:E11,MODE(IF(E4:E11 <> 0,MATCH(E4:E11,E4:E11,0))))}

The formula works but only when 3 or more entries are in the list. I am looking to calculate this based on a list that is part of a template making up a form, so the list is pre-defined as being 8 entries long and defaults to blank cells when the worksheet "template" is opened. Additionally, there may only be one entry, or two, so less than the three required to use the formula above to calculate an automated fill.

The table this forms part of is below:

The list is from E4 to E11. The entries in this list can be one of five possibilities (5, 10, 20, 50) for each row. How do I calculate (into E12) the most common number if there is only 1 or 2 entries in the list? 

Additionally, if there are an even number of entries, how would I then decide which is the most common number based on say which of the "levels of detail" had the longest combined total length?

Hopefully that all makes sense, I have attached the example worksheet from the image above in case that is of any assistance.

Jeff

[#2]

@Jeff_Ho 

I believe you could use the frequency function (see attached). You may be able to shorten the formula some if you have the LET function as some parts are redundant.

I'm assuming total length will be zero if level of detail is zero, so I didn't include a test/condition for that.

[#3]

Thanks @JMB17 

Your solution works perfectly, thanks for taking the time to solve this one for me, it is much appreciated.

[#4]

I'm glad to hear that worked for you.