conditional formatting - highest and 2nd highest

Post Reply
Hans Meiser
Posts: 4
Joined: Sat Jun 01, 2019 7:38 pm

conditional formatting - highest and 2nd highest

Post by 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!
Attachments
Mount and Blade 2.xlsx
(9.15 KiB) Downloaded 238 times
robert-l
SoftMaker Team
SoftMaker Team
Posts: 15
Joined: Mon Dec 03, 2018 9:51 am

Re: conditional formatting - highest and 2nd highest

Post by 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
Posts: 4
Joined: Sat Jun 01, 2019 7:38 pm

Re: conditional formatting - highest and 2nd highest

Post by 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
SoftMaker Team
SoftMaker Team
Posts: 15
Joined: Mon Dec 03, 2018 9:51 am

Re: conditional formatting - highest and 2nd highest

Post by 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
Posts: 4
Joined: Sat Jun 01, 2019 7:38 pm

Re: conditional formatting - highest and 2nd highest

Post by 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
SoftMaker Team
SoftMaker Team
Posts: 15
Joined: Mon Dec 03, 2018 9:51 am

Re: conditional formatting - highest and 2nd highest

Post by robert-l »

Hello Hans,
look at attached PlanMaker file.
Attachments
1und2.pmdx
(5.75 KiB) Downloaded 213 times
Hans Meiser
Posts: 4
Joined: Sat Jun 01, 2019 7:38 pm

Re: conditional formatting - highest and 2nd highest

Post by 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
Post Reply

Return to “PlanMaker 2018 for Windows”