Try

=AVERAGE(--(0&SUBSTITUTE(A1:A5,"<","")))

if there can be a as well then use

=AVERAGE(--(0&SUBSTITUTE(SUBSTITUTE(A1:A5,"<",""),"","")))

it's important that you enter both of these formulas with ctrl + shift &
enter


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



wrote in message
ups.com...
Hi all,

Let's say I want to calculate the average of these numbers:
40, <14, 20, <14, 60

I want the average to show <29.6 but when I do the average function
=average(a1:a5) excel ignores the two <14 and gives the average of 40.

Can anyone help please? I'm faced with this problem almost daily and
forced to do manual calculations.

Thanks in advance,
Mike