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