Averaging numbers but ignoring < and - entries

I have a column of data as illustrated below than contains numbers, blank
cells, dashes and < entries. Is it possible to average only the numbers
ensuring that the divsor is the number of cells that contain number entreis
rather than all cells containing an entry (eg for below example answer should
be 0.137)

0.3

<0.001
0.01

<0.01

0.1

Thank you

On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:

>I have a column of data as illustrated below than contains numbers, blank
>cells, dashes and < entries. Is it possible to average only the numbers
>ensuring that the divsor is the number of cells that contain number entreis
>rather than all cells containing an entry (eg for below example answer should
>be 0.137)
>
>0.3
>
><0.001
>0.01
>
><0.01
>
>0.1
>
>Thank you

If your entries are in, for example, A1:A8, the formula

=AVERAGE(A1:A8)




--ron

How about if there is a ' infront of every entry and - entered into some
cells? I need the ' to trick access into believing that all the entries are
TEXT rather than a mixture of TEXT and NUMBERS.

e.g.
'0.3

'<0.001
'0.01

'<0.01

'-
'0.1

Should give an answer of 0.0842


Thank you

"Ron Rosenfeld" wrote:

> On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:
>
> >I have a column of data as illustrated below than contains numbers, blank
> >cells, dashes and < entries. Is it possible to average only the numbers
> >ensuring that the divsor is the number of cells that contain number entreis
> >rather than all cells containing an entry (eg for below example answer should
> >be 0.0842)
> >
> >0.3
> >
> ><0.001
> >0.01
> >
> ><0.01
> >
> >0.1
> >
> >Thank you
>
> If your entries are in, for example, A1:A8, the formula
>
> =AVERAGE(A1:A8)
>
>
>
>
> --ron
>

Take a look at the VALUE function |:>)

"KIM" wrote:

> How about if there is a ' infront of every entry and - entered into some
> cells? I need the ' to trick access into believing that all the entries are
> TEXT rather than a mixture of TEXT and NUMBERS.
>
> e.g.
> '0.3
>
> '<0.001
> '0.01
>
> '<0.01
>
> '-
> '0.1
>
> Should give an answer of 0.0842
>
>
> Thank you
>
> "Ron Rosenfeld" wrote:
>
> > On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:
> >
> > >I have a column of data as illustrated below than contains numbers, blank
> > >cells, dashes and < entries. Is it possible to average only the numbers
> > >ensuring that the divsor is the number of cells that contain number entreis
> > >rather than all cells containing an entry (eg for below example answer should
> > >be 0.0842)
> > >
> > >0.3
> > >
> > ><0.001
> > >0.01
> > >
> > ><0.01
> > >
> > >0.1
> > >
> > >Thank you
> >
> > If your entries are in, for example, A1:A8, the formula
> >
> > =AVERAGE(A1:A8)
> >
> >
> >
> >
> > --ron
> >

On Wed, 23 Feb 2005 07:45:03 -0800, KIM <KIM@discussions.microsoft.com> wrote:

>How about if there is a ' infront of every entry and - entered into some
>cells? I need the ' to trick access into believing that all the entries are
>TEXT rather than a mixture of TEXT and NUMBERS.
>
>e.g.
>'0.3
>
>'<0.001
>'0.01
>
>'<0.01
>
>'-
>'0.1
>
>Should give an answer of 0.0842
>
>
>Thank you

I don't know. I don't understand how you are getting an answer of 0.0842 with
those values.

If an answer of 0.136667 might be correct, then the *array* formula:

=AVERAGE(IF(ISNUMBER(-SUBSTITUTE(A1:A9,"'","")),--SUBSTITUTE(A1:A9,"'","")))

will strip off leading single quotes and average what is a number.

To enter an *array* formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.


--ron