Removing illogical numbers from an average

[Anonymous]

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

[swuehl · Accepted solution]

I think you need to embed the conditional into your aggregation (sum) function, not the other way round.

Try something like

=avg( if([Quality Rating Int]>24, [Quality Rating Int]))

You can also use a so called set expression to filter your Ratings:

=avg( {<[Quality Rating Int] = {">24"}>} [Quality Rating Int])

Hope this helps,

Stefan

[Anonymous]

Brilliant, thanks so much for you help. Worked first time perfectly.