Highlight top 3 values

[liltbrockie]

Whats the quickest way to do this I'm having a mental block!

6 Fields in a record, each field contains a number, how can I use conditional formatting to highlight the top 3 Values?!?

Eg:

Field1 Field2 Field3 Field4 Field5 Field6

23       25        21       28       20       90

 

Highlight Fields 2, 4 and 6

 

 

Edited November 16, 2016 by liltbrockie

[Wim Decorte]

conditional format, calculation with a Let() that does this:

 

- use List(Field1 ; Field2; ... ) to create a list in a calc variable

- use any of the custom functions to sort the list

- then an evalation wither self = getvalue( sorted_list ; 1 ) or self = getvalue( sorted_list ; 2 ) or self = getvalue( sorted_list ; 3 )

 

 

As an aside: when you start using sequentially numbered fields, it usually means there is need to break that out into a related table.

 

[comment]

This is going to be awkward any way you look at it, because Filemaker has no mechanism for sorting fields. Obviously, these values should be in individual records of a related table - and I am not saying this as an aside. Even a repeating field would be better than 6 individual fields.

If I had to do this with your current structure, I would compare each field with all its 5 siblings, e.g. Self > Field 2, Self > Field 3, etc. Then, if 3 or more of these comparisons returned true, the field is in the top 3 (leaving aside the problem of ties).

Edited November 16, 2016 by comment