Share via

MAX formula in Microsoft Excel question

Anonymous
2022-01-02T10:23:52+00:00

I am using the MAX function in Microsoft Excel to find the highest value in each row. I've got a sheet of info with well over 1,000 rows, so this is the only practical way to do this. The problem seems to be when it hits a row where the value is under 1,000, it simply doesn't highlight the highest value in that row.

I'm using the very basic formula =F2=MAX($F2:$M2). Anyone have any idea what I'm doing wrong?

Also, what formula do I use to find the SECOND highest value in each row?

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
Answer accepted by question author
  1. Ashish Mathur 101K Reputation points Volunteer Moderator
    2022-01-03T23:25:51+00:00

    Hi,

    I typed 1 in any cell and then copied it. I then selected the filled up cells of columns E to I. Right click > Paste Special > Multiply > OK. This process converted the "numbers stored as text to numbers". Then i applied conditional formatting.

    1 person found this answer helpful.
    0 comments

15 additional 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. HansV 462.4K Reputation points MVP Volunteer Moderator
    2022-01-02T12:04:45+00:00

    The values are left-aligned. Could some of them be text values?

    Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then make the copy available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Post a link to the uploaded and shared file in a reply here.

    0 comments
  3. triptotokyo-5840 36,676 Reputation points Volunteer Moderator
    2022-01-02T14:00:37+00:00

    Why don’t you just introduce a column on the far left-hand side of your data and enter the formula:-

    =MAX(E1:M1)

     - (based on your range) and copy down the column?

     If you initially do this copy down process on a row by row basis you should soon see where the error(s) is.

    0 comments
  4. Anonymous
    2022-01-02T14:23:15+00:00

    If that is true (some values are text) and you cannot change this behaviour then perhaps this formula in CF will do:

    =AND(--F2=MAX(--($F2:$M2)),F2<>"")

    Jan

    0 comments