none
Compare 3 or more columns in Excel and see which is greater RRS feed

  • Question

  • Hello Excel Developers, 

    I am reaching out to see if you can help me in my Excel formula problem. I have a data set that contains dates that I need to analyze. So if a certain column is greater than the rest, I am looking for a formula that would say, if Column E is greater than Columns F,G,H,I,J,K then REVIEW else NO ACTION. 

    The below formula does not work.

    =IF(OR(E11>F11,E11>G11,E11>H11,E11>I11,E11>J11,E11>K11),"REVIEW","NO ACTION")

    Best regards, 

    Jackie




    • Edited by IamJackie Monday, July 22, 2019 11:06 PM
    Monday, July 22, 2019 9:08 PM

Answers

  • There might be an issue with the date format otherwise the formula works fine on my end.

    Refer to the image below...


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by IamJackie Thursday, July 25, 2019 2:52 PM
    Tuesday, July 23, 2019 3:51 PM

All replies

  • You may try something like this...

    =IF(E11=MAX(E11:K11),"REVIEW","NO ACTION")

    The above formula assumes that if E11 is the max, no matter if any other cell in F11:K11 also contains the max value, the formula will return "REVIEW".

    OR

    =IF(E11>MAX(F11:K11),"REVIEW","NO ACTION")

    The above formula will return "REVIEW" only if the value in E11 is greater than any value in F11:K11.


    Subodh Tiwari (Neeraj) sktneer

    Tuesday, July 23, 2019 1:13 AM
  • Hello Subodh!

    Appreciate your help! I tried both formula and it works on majority of the files. However, there are a couple of files that returns REVIEW even when it doesn't have to be. Not sure if its because some columns are blank or null which is causing the problem. To illustrate, here is a screenshot of the data set: 

    On the second row, it shows that Column J is greater than Col E so it should be tagged as NO ACTION. Third row shows the same scenario, Columns I, J, K are greater than Col E so it should be NO ACTION. 

    The rest of the rows have the same problem. I really appreciate your help!

    Tuesday, July 23, 2019 3:24 PM
  • There might be an issue with the date format otherwise the formula works fine on my end.

    Refer to the image below...


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by IamJackie Thursday, July 25, 2019 2:52 PM
    Tuesday, July 23, 2019 3:51 PM
  • Thanks so much Subodh! I had to create a macro to format the dates as mm/dd/yyyy and that solved the problem! Appreciate the help! 
    Thursday, July 25, 2019 2:53 PM
  • You're welcome! Glad I could help.

    Subodh Tiwari (Neeraj) sktneer

    Thursday, July 25, 2019 3:05 PM