1 Reply Latest reply on Aug 9, 2017 5:39 PM by swaroop.gantela

    Nested if statement for multiple dimensions/measures

    Kari Cook

      Hi All


      I have a workbook which has different dimensions, that I need to benchmark using a calculation, against different measures. At the moment, the only way I've been able to achieve this, is by having multiple sheets and separate calculations, which go onto the colour mark to give the figures a benchmark (listed 2 below, but have about 9, in the same format):-


      Calculated field 1:


      IF [Overall] >= [BM Overall] THEN 'GREEN'
      ELSEIF [Overall] < ([BM Overall]*0.95) THEN 'RED'
      ELSE 'AMBER'


      Calculated field 2:


      IF [Course Teaching] >= [BM Teaching] THEN 'GREEN'
      ELSEIF [Course Teaching] < ([BM Teaching]*0.95) THEN 'RED'
      ELSE 'AMBER'


      I just wonder if there's a way to have all the separate calculations, in one calculation, and still be able to apply it correctly, with it working individually for each dimension etc. I did try a couple way, which made a valid calculation, but when applied, it didn't work.


      The reason I'm asking, is that the workbook becomes increasingly big in size, with all the different sheets and calculations, and does get slow.


      Any help would be much appreciated!


      Many thanks,