If it is a true #N/A error, then we will combine our solutions:

=AVERAGE(IF(ISNUMBER(--SUBSTITUTE(A1:A100,"x","")),--SUBSTITUTE(A1:A100,"x","")))

Still an array formula.
--
Gary''s Student - gsnu200844


"ryguy7272" wrote:

Thanks GS! There is actually a few "x" things and a couple "NA" things
hanging out in the range. I tried this:
=AVERAGE(--SUBSTITUTE(--SUBSTITUTE(A25:A40,"x",""),"NA",""))

To no avail.

Any other ideas?

Thanks,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Gary''s Student" wrote:

With values in column A:

=AVERAGE(--SUBSTITUTE(A1:A100,"x",""))

This is an array function that must e entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200844


"ryguy7272" wrote:

I am trying to figure out a way to find an average of a range of cells, most
of which contain an "x" (between quotes) character. For instance, I have
several metrics like 12.2x, 10.9x, and 8.9x, for EBIT line items.

This will eliminate the x ind get me results in ColumnB:
=LEFT(A27,LEN(A27)-1)

But I wanted to try to do it without using a helper column.

This CSE function will get me the average of a range with zeros and N/A stuff:
=AVERAGE(IF(ISNUMBER(B27:B42),B27:B42))

I wanted to combine everything into one single cell, if possible. I guess
the first function can't be used on an array; that's pretty much the problem.
Is there a workaround?

Thanks,
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.