One way

=SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/COUNTA(A1:A200)

if the blanks are from null strings like "" use

=SUMPRODUCT(--(0&SUBSTITUTE(A1:A200,"<","")))/SUMPRODUCT(--(A1:A200<""))

--

Regards,

Peo Sjoblom

"KIM" wrote in message
...
The formula worked when I had all the cells filled with an entry (as you
said) however in some of my other data I have some blank cells too so
Answer
2 and 3 won't work. Is there anyway round this?

example data
0.01

0.001
<0.001

0.01
<0.1

i.e I would like the average of all the numbers = 0.0244

Just to confuse the issue I want to link the data into an access database.
The problem with this is that access cannot cope with information in mixed
formates (i.e. a field can only be numerical or text) The only way around
this I have found so far it to trick access into believing that all the
data
is text by inserting ' infront of everything (numbers, < and -) using a
macro. So will having ' infornt of all the entries affect the answer to
the
above question?

Sorry this is so complicated

Thank you

"Bernie Deitrick" wrote:

KIM,

Answer 1:

=AVERAGE(A1:A10)

But this ignores the cells with the < signed values. For your example,
this
will return 11.

Answer 2:

Array enter (enter using Ctrl-Shift-Enter) the formula

=AVERAGE(IF(LEFT(A1:A10,1)="<",VALUE(MID(A1:A10,2, 15)),A1:A10))

where A1:A10 are the cells with the values. Note, all the cells musxt be
filled - otherwise, they will be treated as 0. For your example, this
formula will return 7.

Answer 3:

Array enter (enter using Ctrl-Shift-Enter) the formula


=AVERAGE(IF(LEFT(A1:A5,1)="<",IF(VALUE(MID(A1:A5,2 ,15))<100,VALUE(MID(A1:A5,
2,15)),""),A1:A5))

Though this will also ignore <1000, <500, etc. as long as the resulting
number (to the right of the < sign) is greater than or equal to 100.


HTH,
Bernie
MS Excel MVP

"KIM" wrote in message
...
Question 1. How do I average a column that contains a mixture of
numbers
and
less than entries?
e.g. 12
<1
10
<5

Question 2. Is it possible to include the less than values in the
average
calculation (i.e so excel ignores the less than sign and calcualted
the
avearge using the number that is next to the less than sign)?

Question 3. Is it possible to include some less than values (e.g. <10)
in
the average calculation but ignore say other less than values (e.g.
<100)?

Thank you!