On Wed, 23 Feb 2005 07:45:03 -0800, 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

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