conditional formatting - highest and 2nd highest

Post your questions, bug reports and suggestions here for PlanMaker 2018 for Windows.

Moderator: SoftMaker Team

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

conditional formatting - highest and 2nd highest

Post by Hans Meiser » Sat Jun 01, 2019 8:11 pm

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 3 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 » Mon Jun 03, 2019 1:40 pm

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 » Mon Jun 03, 2019 3:06 pm

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 » Tue Jun 04, 2019 2:20 pm

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 » Tue Jun 04, 2019 7:26 pm

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 » Wed Jun 05, 2019 2:03 pm

Hello Hans,
look at attached PlanMaker file.
Attachments
1und2.pmdx
(5.75 KiB) Downloaded 2 times

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

Re: conditional formatting - highest and 2nd highest

Post by Hans Meiser » Wed Jun 12, 2019 2:10 pm

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”