3 Replies Latest reply on Mar 26, 2012 3:44 AM by . Matthew

    Help with a calculation

    . Matthew

      Can anyone offer ideas that can help me out with a calculation I am stuck with?

       

      I have attached a workbook with some example data to illustrate my problem.  The example shows a number of colleges, each with a number of students. These students study a variety of subjects and their exam results are recorded.  I am looking to show a table of top levels statistics that vary as I filter between the colleges – things like number of students, number of subjects etc. However, where I am struggling is a calc that will show the average number of subjects studied per student. On the face of it, it sounds like a very simple calculation and to do it in a static manner would be straight forward enough but trying to a get a dynamic version has stumped me.  If I use a table calc and show each individual student I can get the result but what I am after is a single (total) figure on its own.  I have made several attempts as you’ll see but they always return a number smaller than 1 which cannot be correct.

       

      I have reached a point of intense frustration so any suggestions will be greatly appreciated.

        • 1. Re: Help with a calculation
          Alex Kerin

          Your calculation is the total distinct subjects (11) divided by the distinct students (15), giving 0.73.

           

          Changing countd[subjects] to count [subjects] gives you what you need I think

           

          No need for the table calculation.

           

          EDIT: Retract all of that - I hadn't seen the levels, hence the countd

          • 2. Re: Help with a calculation
            Alex Kerin

            Okay, you still don't need the table calculation. First, let's calculate the total subjects taken by all students (regardless of the number of levels)

             

            countd(str([Student])+[Subject])

             

            and then we can simply divide this by the number of students

             

            countd(str([Student])+[Subject])/countd([Student])

            • 3. Re: Help with a calculation
              . Matthew

              Alex,

               

              Appreciate your help here.  Is is spot on.  I'd got caught up in Table Calcs and had missed the idea of counting the number of the combination of the two fields.

               

              Thanks again.