2 Replies Latest reply: Aug 26, 2012 5:43 AM by Dominic Elfick RSS

    Removing illogical numbers from an average

    Dominic Elfick

      I am trying to calculate an average service score for deliveries. The scores are Bad, poor, fair and excellent and have been coverted into 25, 50, 75 or 100 unfortunately in teh conversion process unreported values (of which there are a lot) have been converted to -1 and N/A values have been converted to 0. This is making it very difficult to calculate a correct average.

       

      Without having to go back to the programmers and getting them to change something on the load is there a way to exclude these "incorrect" values and get a proper average? I have tried turning them into nulls, but then I get no average at all,

       

      and I have turning them into 1's but that also throws a weird average

      if([Quality Rating Int]>24,sum ([Quality Rating Int]),1) / if([Quality Rating Int]>24,NumericCount ([Quality Rating Int]),1)

       

      I have tried using the rangeavg but that throws "funnys" too

      rangeAvg ([Service Rating Int])

       

      I am sure there is a really simple way to do this but I can not find it. Any help is greatly appreciated.

       

      Thanks