Forum Discussion

Jeff_Ho's avatar
Jeff_Ho
Copper Contributor
Dec 09, 2020

Determine most common text in an incomplete list

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

3 Replies

  • JMB17's avatar
    JMB17
    Bronze Contributor

    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.

    • Jeff_Ho's avatar
      Jeff_Ho
      Copper Contributor

      Thanks JMB17 

       

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

      • JMB17's avatar
        JMB17
        Bronze Contributor
        I'm glad to hear that worked for you.

Resources