2 Replies Latest reply on Sep 11, 2018 5:28 AM by Kevin Ward

    Tracking a hit rate in a target range

    Kevin Ward

      Hi,

       

      We start a number of samples per day. I am tracking this and want to show the number of times we hit the desired number of starts in a day for each month.

       

      Our upper limit is fixed at 350.

      Lower limit is what the daily sample starts Target for the month is (on the attached workbook this is manually set at 300) less 20%, so for the attached this has been manually set at 240 (300 * 0.8)

       

      Therefore a hit is when we have sample starts in a day between 240 and 350.

       

      What i would like:

       

      1) Return a value of how many hits in a month we have achieved?

      2) The daily sample starts target for the month - this changes each month. At the moment I set it manually but this loses the history of previous months. What is the best way to set this target without losing history?

      3) The "Lower" value is 0.8* "target". Based on the answer to (2) how best to aut-calculate this parameter?

       

      I also would like to show the monthly number of sample starts. We have a total target of starts for the month so can i add a text box that shows #starts/monthly target as a %?

       

      Thanks,

        • 1. Re: Tracking a hit rate in a target range
          Zhouyi Zhang

          Hi, Kevin

           

          Please find my solution attached to your question.

           

          I am not sure your last question regarding the %, can you give example?

           

          ZZ

          • 2. Re: Tracking a hit rate in a target range
            Kevin Ward

            Hi ZZ,

             

            You have solved it in part, thanks!

             

            1) a hit is where we have sample starts between "Monthly Target less 20% (where you have added "lower Value" Measure)" and "350 (our upper limit)".

            Your calculation only returns hits where the value is =>350.

            In the example, June (with Monthly daily target of 300) should return 10 hits

             

            2 & 3) Your answers work great on this, thanks! - On the attached, can i add these lines all to the same chart? it auto separates them underneath (see Sheet 1)

             

            Regarding the Totals, I would like to add a Total Monthly Target, similar to the daily target but a sum of the whole month. For example in June the Total Monthly target is 9500 sample starts. We did 9451 samples so achieved 99.5% of target.

             

            Thanks again,

            Kevin