null values in average

[rcorcoran]

Hi How can I make the following exclude the null or 0 values.  It is giving me an average over all even if value is null whereas I want an average of just where there are values greater than 0??

Any help appreciated

Pick(Match(ValueList('Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

'Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

num(((Avg({$<[Acquired Infection]={4},ReferredMM={3}>}ActualValue)))), '##0.0')

[anbu1984 · Accepted solution]

=num(((Avg({$<[Acquired Infection]={4},ReferredMM={3}>}(If(ActualValue,ActualValue))))), '##0.0')

[Gysbert_Wassenaar]

Pick(Match(ValueList('Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

'Rate of new cases of Clostridium Difficile associated diarrhoea in acute hospitals per 10,000 bed days used'),

num(((Avg({$<[Acquired Infection]={4},ReferredMM={3},ActualValue={'>0'}>}ActualValue)))), '##0.0')

[rcorcoran]

Both of the solutions above work thanks so much to you both

[rcorcoran]

Thanks anbu works great

Rhona Corcoran

[rcorcoran]

Thanks Gysbert works great

[rcorcoran]

Thanks Anbu works great