Compute MODE of all numbers split across multiple worksheets

{ 10 Comments }

By Ashish Mathur · · , , · Leave a comment

Assume numbers are typed in range A1:A2 of multiple worksheets in a workbook.  The task is the compute the MODE of these numbers.  Mode is defined as the value which appears most frequently in a range of cells.  So, if one types 1,3,4,3,5,6 in range A1:A6, then the mode will be 3 – 3 appears maximum number of times in the range.

In MS Excel, there is a built in way to compute the MODE.  The formula for the same is

=MODE(A1:A6)

Unfortunately, MODE() is not a 3D function and therefore, something like this return a #REF error

=MODE(sheet1:sheet3!A1:A6)

This behavior seems somewhat vague because other basic Mathematical and Statistical functions such as SUM(), COUNT(), AVERAGE(), MAX(), MIN(), VAR(), and STDEV() work just fine across multiple worksheets.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem.

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).  In Power Query, there is a feature to append data from multiple worksheets into one running range.  Once this is done, the running range can be transferred to an MS Excel worksheet on which the MODE function can be used.

Solution for all versions of MS Excel

To compute MODE across multiple worksheets, you may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Hi Ashish. The formula here is not correct yet. It’s calculating the location of the mode in the Top:Bottom frequency array, but using that location as the row reference in your index function on G1:G3 to output the sheet on which this occurs. Also, it’s forcing the output to be hard-entered cell reference “!A1” on the indexed sheet reference.

    So if you change the order of numbers on sheet 1 from 25;34 to 34;25, the position of the mode in your frequency array moves from array element 1 to 2, which then looks up row 2 in your G1:G3 sheet array, giving ‘sheet 2’, and then reference A1 on sheet 2, which is not the right answer.

    Also, if you change the numbers to 11;12 on Sheet 1, 13;25 on Sheet 2, and 25;34 on Sheet 3, the mode should be 25, but because this is element 4 in the frequency array, the formula looks for the 4th row in G1:G3 – Error.

    Cheers

    Reply

    • Hi,

      Thank you for taking the time out to review my solution. Please find my revised solution in this workbook.

      I have not tested it for empty cells/text entries in A1:A2 of all sheets. I have assumed that all cells in range A1:A2 across all sheets will be filled up.

      Reply

  • Hi Ashish,

    I am looking for a formula which can give me the MAX value across multiple tabs based upon the defined list of Tab names available in a Named range.
    Range is Dynamic so we should have the flexibility to add new tabs or remove it from the list.

    Sample file is available in the below link.

    https://www.hidrive.strato.com/lnk/iiojxc6h

    Let me know if you need more details on my query.

    Regards,
    Pranay Shah

    Reply

    • Hi,

      Try this

      1. Click on cell A2 and press Ctrl+B
      2. Select range A2:A8 and press Ctrl+T
      3. In cell E2, enter this formula

      =SUMPRODUCT(MAX(INDIRECT(“‘”&A3:A8&”‘!B3”)))

      Hope this helps.

      Reply

      • Hi Ashish,

        I tried this formula but it gives a #N/A error, also I don’t want the range to be made as a table as that will be used for other purposes also and it is linked with the macros in the main file. Though I understand that it helps because in formula it auto updates the range but in my case it may not help.

        Regards
        Pranay Shah

        Reply