6 Replies Latest reply on May 28, 2013 11:03 AM by Justin Smith

    Please Help - Average of Averages

    Justin Smith

      Alright everyone,

       

      I've got a list of thirteen questions from an evaluation form that I need to average per each question then average those averages. I have a Calculation for each question converting the response to a number. They look like this:

       

      Name of Calculation is "1"

      Formula:

       

      Case [question1]

          when "A" then 4

          when "B" then 3

          when "C" then 2

          when "D" then 1

      End

       

      Then, still for each question, I have an average of the results like this:

       

      Name of Calculation is "This is the name for question one so that reviewers know what the criteria was"

      Formula:

       

      avg([1])

       

       

      So I have all thirteen done like this. I need to average them all for a calculation called "Total Average". I have this working by totaling all the thirteen questions and dividing by thirteen when all questions have a value.

       

      The problem is that there is an option on all questions for the result to be "E" for Not Applicable which should result in NULL. This displays correctly on the per question average if all are NULL by simply not displaying anything which is fine. However, it makes the "Total Average" filed display "All" instead of averaging all other questions. On top of that, if there is a NULL value, it shouldn't have a divisor of thirteen since that field represent Not Applicable.

       

      Clear as mud? I thought so.

       

      I've attached a shot of one that has NULL values for multiple questions, and one that is completely filled out and averaging properly.

       

      Thanks in advance,

      Justin

        • 1. Re: Please Help - Average of Averages
          Alex Kerin

          Depending how your table is set up, you may be able to turn on grand total, which will actually average across the entire data set. It that doesn't work, then total(avg([values])) should.

          1 of 1 people found this helpful
          • 2. Re: Please Help - Average of Averages
            Justin Smith

            Alex,

             

            Thanks for the guidance. I am not saying that they didn't work, simply I don't know what I'm doing with this. My fear is it is one of those so close to your eyes in front of you that you can't see it. I have attached a set of dummy data with the thirteen questions showing an average for each one. All I need is to average those averages (ignoring the questions that have a NULL average). For example, the attached should have a "Total Average" of 3.30.

             

            The calculation needs to be on a separate sheet as the design of the dashboard that pulls all this together requires it. I tried doing this on the attachment, and I still can only get NULL value for a "Total Average" which causes it to display All.

             

            Any help is greatly appreciated,

             

            Justin

             

            PS I like the new avatar

            • 3. Re: Please Help - Average of Averages
              Justin Smith

              Also,

               

              I restructured the data to have a number instead of letter on the database level. I think it is cleaner that way, and I have control over the database.

               

              Justin

              • 5. Re: Please Help - Average of Averages
                Jim Wahl

                I think the data format requires you to use a manual calculation for average; something like:

                 

                Total Average =

                (ZN(AVG([question1])) + ZN(AVG([question2])) + ZN(AVG([question3])) + ... )

                /

                (IIF(COUNT([question1]) > 0, 1, 0) + IIF(COUNT([question2]) > 0, 1, 0) + IIF(COUNT([question3]) > 0, 1, 0) + ...)

                 

                If you reshape the data with one question column and one response column (make it tall instead of wide), you could do something like:

                Total Average =

                WINDOW_AVG(AVG([Response])) or, in this case the equivalent, TOTAL(AVG([Response]))

                 

                The reshaping also makes it easier to work with the data---you only have two pills instead of 13.

                 

                One issue with reshaping is that you'll probably want all of the questions in the rows, even if there is no response value. For example, question3 has no responses, but you'd still want this row in your data so that you can, for example, generate a view with each question and a tally of responses, showing null for question3. 

                 

                Jim

                • 6. Re: Please Help - Average of Averages
                  Justin Smith

                  Jim,

                   

                  Thank you so much for your reply. Through no intelligence on my own part, I stumbled upon this only minutes ago. My formulas are almost identical to yours, except yours is easier to comprehend, and I will be adapting mine to read like yours. (I used a separate calc for the denominator, and somehow window_avg(zn(q1)+zn(q2) etc. for the numerator.)

                   

                  Thanks for your help,

                  Justin