Hi all, I am trying to build a KPI report for my teams, I have eleven KIP’s and currently using basic “=$B$16/B2” formulas as I am doing it one team at the time. My question is what formula can I use to have all data dumped into the worksheet and then extract it per teams. I have attached an example of the report as well as the KPI goal. What we looking to retrieve is the percentage to goal on each KIP.
Thank you for the help.
REP NAME AHT DISP NS ACW TFR RRT H3 H10 TOP BOTTM D S KPI Goal
Carter 873 99.36% 16.67% 264 31.21% 0.00% 69.43% 4.46% 100.00% 0.00% 10.53% AHT =<800
Adrian 595 92.21% 20.83% 155 29.87% 7.79% 77.92% 3.90% 100.00% 0.00% 0.00% DISP =>98%
Alejandro 477 99.18% 25.62% 40 19.26% 0.00% 72.95% 2.46% 66.67% 33.33% 12.50% NS =<20%
Angel 767 99.52% 19.05% 211 24.76% 0.00% 70.48% 7.62% 0.00% 0.00% 8.70% ACW =<150
Gab 854 96.57% 26.47% 288 19.43% 0.57% 62.29% 5.71% 50.00% 0.00% 0.00% TFR =<25%
Campos 1,107 100.00% 32.20% 209 35.04% 0.00% 59.83% 12.82% 100.00% 0.00% 5.56% RRT =<2%
Franklin 664 96.47% 29.27% 203 20.00% 0.59% 76.47% 7.65% 0.00% 0.00% 0.00% H3 0.72
Ali 866 100.00% 10.26% 242 30.77% 0.00% 61.54% 10.26% 0.00% 0.00% 0.00% H10 =<7%
Garcia 829 98.53% 20.90% 224 17.65% 0.00% 61.03% 11.03% 100.00% 0.00% 0.00% TOP =>85%
Maril 817 96.18% 28.57% 180 25.19% 0.00% 70.99% 6.87% 50.00% 50.00% 9.09% BOTTM =<5%
Ray 829 98.43% 13.71% 118 27.75% 0.00% 68.06% 5.76% 100.00% 0.00% 13.33% D S =<7%
Sony 794 98.09% 30.73% 92 27.75% 0.00% 67.46% 8.61% 0.00% 0.00% 8.00%
770 98.06% 23.57% 174 24.78% 0.43% 68.70% 6.84% 78.57% 14.29% 6.23%
- + - - - - + - + - -
800 98% 20% 150 25% 2% 72% 7% 85% 5% 7%
Percentage to goal
REP NAME OS AHT OS DISP OS NSRV OS ACW OS TFR OS TFRRT OS H3 OS H10 TOP TWO BOTTOM BOX DEFECT SCORE
Carter 92% 101.39% 83.35% 0.57 80.10% 96.43% 156.95% 117.65% 100% 66%
Adrian 134% 94.09% 104.15% 0.97 83.70% 0.26 108.22% 179.49% 117.65% 0%
Alejandro 168% 101.20% 128.10% 3.75 129.80% 101.32% 284.55% 78.44% 56%
Angel 104% 101.55% 95.25% 0.71 100.97% 97.89% 91.86% 0.00% 2000% 80%
Gab 94% 98.54% 132.35% 0.52 128.67% 3.51 86.51% 122.59% 58.82% 0%
Percentage to goal
REP NAME OS AHT OS DISP OS NSRV OS ACW OS TFR OS TFRRT OS H3 OS H10 TOP TWO BOTTOM BOX DEFECT SCORE
Campos
Franklin
Ali
Garcia
Maril
Ray
Sony
Thank you for the help.
REP NAME AHT DISP NS ACW TFR RRT H3 H10 TOP BOTTM D S KPI Goal
Carter 873 99.36% 16.67% 264 31.21% 0.00% 69.43% 4.46% 100.00% 0.00% 10.53% AHT =<800
Adrian 595 92.21% 20.83% 155 29.87% 7.79% 77.92% 3.90% 100.00% 0.00% 0.00% DISP =>98%
Alejandro 477 99.18% 25.62% 40 19.26% 0.00% 72.95% 2.46% 66.67% 33.33% 12.50% NS =<20%
Angel 767 99.52% 19.05% 211 24.76% 0.00% 70.48% 7.62% 0.00% 0.00% 8.70% ACW =<150
Gab 854 96.57% 26.47% 288 19.43% 0.57% 62.29% 5.71% 50.00% 0.00% 0.00% TFR =<25%
Campos 1,107 100.00% 32.20% 209 35.04% 0.00% 59.83% 12.82% 100.00% 0.00% 5.56% RRT =<2%
Franklin 664 96.47% 29.27% 203 20.00% 0.59% 76.47% 7.65% 0.00% 0.00% 0.00% H3 0.72
Ali 866 100.00% 10.26% 242 30.77% 0.00% 61.54% 10.26% 0.00% 0.00% 0.00% H10 =<7%
Garcia 829 98.53% 20.90% 224 17.65% 0.00% 61.03% 11.03% 100.00% 0.00% 0.00% TOP =>85%
Maril 817 96.18% 28.57% 180 25.19% 0.00% 70.99% 6.87% 50.00% 50.00% 9.09% BOTTM =<5%
Ray 829 98.43% 13.71% 118 27.75% 0.00% 68.06% 5.76% 100.00% 0.00% 13.33% D S =<7%
Sony 794 98.09% 30.73% 92 27.75% 0.00% 67.46% 8.61% 0.00% 0.00% 8.00%
770 98.06% 23.57% 174 24.78% 0.43% 68.70% 6.84% 78.57% 14.29% 6.23%
- + - - - - + - + - -
800 98% 20% 150 25% 2% 72% 7% 85% 5% 7%
Percentage to goal
REP NAME OS AHT OS DISP OS NSRV OS ACW OS TFR OS TFRRT OS H3 OS H10 TOP TWO BOTTOM BOX DEFECT SCORE
Carter 92% 101.39% 83.35% 0.57 80.10% 96.43% 156.95% 117.65% 100% 66%
Adrian 134% 94.09% 104.15% 0.97 83.70% 0.26 108.22% 179.49% 117.65% 0%
Alejandro 168% 101.20% 128.10% 3.75 129.80% 101.32% 284.55% 78.44% 56%
Angel 104% 101.55% 95.25% 0.71 100.97% 97.89% 91.86% 0.00% 2000% 80%
Gab 94% 98.54% 132.35% 0.52 128.67% 3.51 86.51% 122.59% 58.82% 0%
Percentage to goal
REP NAME OS AHT OS DISP OS NSRV OS ACW OS TFR OS TFRRT OS H3 OS H10 TOP TWO BOTTOM BOX DEFECT SCORE
Campos
Franklin
Ali
Garcia
Maril
Ray
Sony
