12 Replies Latest reply on Oct 29, 2014 1:07 PM by Diane Barbee

    Summing Average Rankings of Teams by Group

    Diane Barbee

      I have a sales floor that is broken into Teams (Blue, Red, Green, etc.). Each individual is ranked on 3 individual criteria (# of Docs sent out, # of Docs returned, # of clients enrolled) and then those 3 are combined to get an "Average Rank" overall. The calc I use for that is basic ([Rank of Docs Out]+[Rank of Resolution Units]+[Rank of Units ])/3 and all of that seems to work fine. I have the individual ranking of each individual in each category and then the average ranking of the 3 in the 4th column. The problem comes in as I group the individuals by their teams. I want to get a subtotal for each team that calculates the average ranking of all individuals on the team. If I use the subtotal feature it RANKS the teams on totals with the highest number getting the 1st ranking but that doesn't work! Below is an example of what I'm trying to accomplish. How do I find the "Averange Rank of Each Team" - meaning (4.67+15+17.33+21.67+37.33+36.67+42)/7 and so on?? If I try to subtotal it just puts 1, 2, 3, etc.

       

         

      TeamEmployeeRank of Docs Out along Team, Employee NameRank of Resolution Units along Team, Employee NameRank of Units  along Team, Employee NameRank Average along Team, Employee Name
      BlackJane9.004.001.004.67
      Joe12.0029.004.0015.00
      Robert40.002.0010.0017.33
      Jordan16.0042.007.0021.67
      Michael38.0027.0047.0037.33
      Vanessa23.0050.0037.0036.67
      Diane29.0051.0046.0042.00
      BlueLeah16.003.007.008.67
      Liz5.007.0017.009.67
      Brent13.0010.0017.0013.33
      Kaleigh42.005.0023.0023.33
      Callie22.0044.0037.0034.33
      Sara43.0044.0037.0041.33
      Sandy46.0042.0047.0045.00
      GoldHeather2.001.003.002.00
      Nancy23.005.0012.0013.33
      Jamal10.0016.0026.0017.33
      Ronald49.0019.007.0025.00
      Kevin33.0029.0033.0031.67
      Jessica29.0029.0037.0031.67