Share via

Problems with Mode formula in Excel

Anonymous
2025-02-13T20:20:50+00:00

Good day.

I apologize in advance for any errors in communication. I have six tables, 8 x 10, one above the other, and the data includes a header for both rows and columns. At the end of row and column, I have a formula to identify the header associated with the highest value in the row (A-J) and the one in the column (January-August). What I need is a formula to give me the mode for the highest values for all six tables at the same time, meaning, that after filling the data, I can automatically get the most repeated header for all six tables, one for rows and one for columns, as marked in blue and yellow at the very bottom of the tables. The data will not be natural numbers, meaning the possibility of draws can be dismissed. What is the easiest way to do this?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Rich~M 20,355 Reputation points Volunteer Moderator
    2025-02-14T01:44:11+00:00

    I believe I have formulas that will work for you.

    In Column J for the max for each row I used:

    =XLOOKUP(MAX(B3:I3),B3:I3,$B$2:$I$2)

    In the max rows, Row 13, 27, etc. I used:

    =XLOOKUP(MAX(B3:B12),B3:B12,$A$3:$A$12)

    Both of these formulas will result in #N/A if there is no data in the Row/Column. That is good. The MODE formulas will replace those with "" so that they will not be counted.

    For the column mode shown in L3 in the screenshot use this formula:

    =LET(range,TRANSPOSE(HSTACK(B13:I13,B27:I27,B41:I41,B55:I55,B69:I69,B83:I83)),INDEX(range,MODE(IFNA(MATCH(range,range,0),""))))

    For the row mode shown in N3 in the screenshot use this formula:

    =LET(range,VSTACK(J3:J12,J17:J26,J31:J40,J45:J54,J59:J68,J73:J82),INDEX(range,MODE(IFNA(MATCH(range,range,0),""))))

    0 comments