1 Reply Latest reply on Jul 8, 2014 6:22 AM by philmodjunk

    help with calculation in conditional formatting

    IzzyMcCarthy

      Title

      help with calculation in conditional formatting

      Post

           I hope there is someone out there with a bit more mathematical knowledge than me, who can help me with a calculation I want to do. 

           I have a form with student's level reports and I need some number fields to automatically change a certain colour, depending on how their actual level compares to the required level. (Example: if their required level is 5, but they have a 4, then it would be orange; if they had a level 2, then it would be red....). 

      Levels are from 1-10 (10 being the highest) There are various level requirements. 

            

           There are 5 conditions. 

           Red - far below required level (more than 1.51  in the negative)

           orange - below level required (up to 1.50 points below level required)

           yellow - meets level (the same number as level required)

           green - above (up to 1.5 points)

           dark green - far above level required (1.51 up to the maximum of 10)

            

           -1.51 or more                        up to -1.5                             0 (same)                         up to +1.5                    from +1.5 1

            

           I hope this is clear enough. 

           Thanks in advance for any help you can give me. 

            

           Izzy. :) 

      Screen_Shot_2014-07-08_at_08.59.50.png

        • 1. Re: help with calculation in conditional formatting
          philmodjunk

               I must assume that there are two fields involved, Score, and Level. Substitute the precise field name references for these names in your expressions.

               You can create one conditional format expression for each color. The order of expressions from top to bottom is important for this to work. FileMaker evaluates these from top to bottom and applies each color in that sequence so a difference between score and level of -1.6 will first produce an Orange color but then that will be replaced by Red when the last expression is evaluated.

               ( Score - Level ) = 0    (Yellow)
               (Score - Level ) > 0     (green)
               (Score - Level ) < 0     (Orange)
               (Score - Level ) > 1.5  (Dark Green)
               (Score - Level ) < -1.5 ( Red )