2 Replies Latest reply on Jun 20, 2017 5:16 AM by Michael Gillespie

    Avg wrongly calculated

    Alessio Rossotti

      Hello,

      I'm trying to display three simple horizontal lines starting from a table where I have upper and lower limits.

      I want to display a line with the maximum of the upper limits, a line with the minimum of lower limits and a line exactly in the middle.

      I've added two aggregate values to the graph: MAX(upper), MIN(lower) and a computed field target as (MAX(upper)+MIN(minimum))/2.

       

      This works for most of the values, but in some cases I have the middle line that is not centered and has a strange value.

      For example I have the values: minimum 137,137,130 - maximum: 145,145,142

      The two lines are computed correctly as 130 and 145, but the line in the middle is set to 140.5. What's happening?

       

      Thanks,

        • 1. Re: Avg wrongly calculated
          Amit Narkar

          Could you share any sample data or workbook?

          • 2. Re: Avg wrongly calculated
            Michael Gillespie

            Alessio:

             

            Can you do some simple debugging to make sure that all the expressions are returning the values you expect?  The technique is to break each component of what you're doing down into its constituent parts so you can see into the calculations.  After each step, drop the calculated field you create onto a blank worksheet to see the results.

             

            1) Create 2 calculated fields for the MIN and MAX values you want to use:

            Min of Lower: MIN([lower])

            Max of Upper: MAX ([upper])

             

            Do those 2 return 130 and 145 respectively?

             

            2) Now create the sum of those 2 fields:

            Sum of Max & Min: [Min of Lower] + [Max of Upper]

             

            Does this return 275?

             

            3) Finally, create an Average calculation:

            Average of Max + Min: [Sum of Max & Min]/2

             

            Does this return 138.5?

             

            Your problem will be found somewhere in there!