4 Replies Latest reply: Mar 11, 2013 10:29 AM by Martha Misquitta RSS

    Display max value and other related fields

      Hello everyone,

      I need help in accomplishing the final product.  See step by step information below: 

       

      Raw data:

      Name
      Dept
      Dept % 1
      Dept % 2
      Role
      MariaLT25%80%Core
      MariaBT55%0%Embed
      JohnLT75%25%Embed
      LisaCT0%100%Core
      LisaLT60%20%Embed

       

      Step 1:

      create column 5. See formula in column title for more information.

      123456
      Name
      Dept
      Dept % 1Dept % 2
      =IF(Dept % 2 >=0.35, '>35%','<35%')
      Role
      MariaLT25%80%>35%Core
      MariaBT55%0%<35%Embed
      JohnLT75%25%<35%Embed
      LisaCT0%100%>35%Core
      LisaLT60%20%<35%Embed

       

      Step 2:

      If duplicate name, select the row that has max value = (Dept %1 + Dep % 2). 

       

       

      Step 3: FINAL:

      After eliminating duplicates,

      Count name by role

      Final Product:

      Dept
      =IF(Dept % 2 >=0.35, '>35%','<35%')
      Count of Name by role = Core
      Count of name by role = Embed
      BT>35%1
      JT>35%
      1
      LT<35%1

      >35%1