Average in script

[samuel_brierley]

Hi all first of all thanks for any help,

I basically have 4 speed measurements that i would like to average in the script however if no speed is measured it is recorded as an X.

how therefore do i only average those that are numbers?

I have tried below but this doesnt like the X's i presume,

[Speed(Lane 1)]+[Speed(Lane 2)]+[Speed(Lane 3)]+[Speed(Lane 4)])/4) as [Average Speed]

[MK_QSL]

Provide data and your required output... please..

[jonathandienst]

Hi

=RangeAvg([Speed(Lane 1)], [Speed(Lane 2)], [Speed(Lane 3)], [Speed(Lane 4)]) as [Average Speed]

HTH

Jonathan

[samuel_brierley]

HI thanks for the input however I cant treat them as zeros because that will lower my average.

[simenkg]

     rangesum([Speed(Lane 1)]+[Speed(Lane 2)]+[Speed(Lane 3)]+[Speed(Lane 4)])/

    (4 - (if([Speed(Lane 1)]='X',1,0) + if([Speed(Lane 2)]='X',1,0) + if([Speed(Lane 3)]='X',1,0) + if([Speed(Lane 4)]='X',1,0))) as [Average Speed]

[samuel_brierley]

thats the logic thanks SImen

[jonathandienst]

=RangeSum([Speed(Lane 1)], [Speed(Lane 2)], [Speed(Lane 3)], [Speed(Lane 4)]) /

RangeCount([Speed(Lane 1)], [Speed(Lane 2)], [Speed(Lane 3)], [Speed(Lane 4)]) as [Average Speed]

[simenkg]

I actually think this one is better:

rangesum([Speed(Lane 1)]+[Speed(Lane 2)]+[Speed(Lane 3)]+[Speed(Lane 4)]/

rangenumericcount([Speed(Lane 1)],[Speed(Lane 2)],[Speed(Lane 3)],[Speed(Lane 4)])

as [Average Speed]

[#9 · Accepted solution]

RangeAvg( if(isnum([Speed(Lane 1)]),[Speed(Lane 1)]),

                         if(isnum([Speed(Lane 2)]),[Speed(Lane 2)]),

                              if(isnum([Speed(Lane 3)]),[Speed(Lane 3)]),

                                   if(isnum([Speed(Lane 4)]),[Speed(Lane 4)])) as [Average Speed]

[simenkg]

Be sure to mark the correct answer as such so that others don't have to read through the whole post to get the right answer.