conditional formatting - highest and 2nd highest

[Hans Meiser]

Hi all,

I have a table with 10 columns and 24 rows.

I have tried to use the conditional formatting to highlight the highest and second highest value per column with a colour each.

I used 'use formula to determine which cells to format' plus the formulas

=C2=MAX($C$2:$L$2) and =C2=LARGE($C$2:$L$2,2) both with an individual shading of the cell background.

This should work, correct? And I have to create two rules (highest, 2nd highest) per row, correct?

Well have tried and it worked for the highest (done them first), but when I did it for the 2nd highest it went crazy (colouring the highest with the 2nd highest colour). Please see attached file.

I hope someone can help.

Thanks!

[robert-l]

Hello Hans,

you can bypass what happens if you pay attention to the order in which the rules are executed. This is the same behavior as in Excel.

It becomes really problematic if values are changed afterwards. Then it can come to some confusion. I need to take a closer look at this.

[Hans Meiser]

Hi Robert,

Shame this simple task is problematic for Planmaker or Excel. Looking forward to your suggestions once you had a closer look.

[robert-l]

Hello Hans,

your problem is that you have duplicate maximum values. In that case, none of them is marked as highest, both are marked as second-to-maximum, third highest is not marked.

Change the formula for the 2nd highest value to
=RANK(C2;$C$2:$L$2;)=(COUNTIF($C$2:$L$2;MAX($C$2:$L$2))+1)

Just for the record, this is not a bug. Behavior is the same as in Excel.

[Hans Meiser]

Hi Robert,

I have copied and pasted your formula, but Planmaker complains 'One of the values/fomulas is invalid or missing'. Any ideas?

[robert-l]

Hello Hans,
look at attached PlanMaker file.

[Hans Meiser]

Hi Robert,
Works like a treat. Thank you so much!

It is weird there is no simpler (shorter formula or standard function) and less work intensive (changing every row) way. It seems like an often needed tool ... okay, only needed it once, but I use spreadsheet analysis only once or twice a year