Search code examples
excelexcel-formula

How to conditionally format non-adjacent cells in Microsoft Excel based on the second highest value

I am preparing a scorecard for a quiz to be played among 6 teams. A part of which looks like the following:

enter image description here

The cell with the highest score is highlighted in Green. I achieved this using an inbuilt formula under "Conditional formatting" on the cells D4, D10, D16, D22, D28 and D34. What I want more is to highlight the second and third highest scores as well. I am facing great difficulty in achieving that because of the non-adjacency of the cells.

I tried plentiful efforts with the LARGE function, but it refuses to accept an array as input and expects a range instead.

I think at this point I should mention that I am using Microsoft® Excel for Mac with a 365 subscription. My machine is running macOS Sonoma. In my failed efforts I suspected that LARGE is usually accepting arrays as inputs in Windows machines.

One more point to emphasise is that I do not want to discard duplicate values in the mentioned cells. I have a tiebreaker rule in the quiz if the multiple total scores become equal.

Solution

  • Conditionally Format Non-Adjacent Cells According to Ranks

    • Select D4:D34 and add the following 3 conditions:

      =IF(ISNUMBER(D4),RANK.EQ(D4,D$4:D$34)=1)
=IF(ISNUMBER(D4),RANK.EQ(D4,D$4:D$34)=2)
=IF(ISNUMBER(D4),RANK.EQ(D4,D$4:D$34)=3)

    • What's with the ISNUMBER()?

    • It takes care of not formatting all the irrelevant cells when 0 is among the top three.

    enter image description here

    • The screenshot illustrates a way to build and test the formulas. The cells E4:G4 contain the same 3 formulas.
    • You can copy/paste all of it horizontally (to the right or left) because only the rows are locked ('$').
    • Since you cannot avoid locking rows, to copy vertically (down or up), you need to copy/paste to the right then cut/paste down or up and copy horizontally as you see fit.