Calculate Average with 'x' in Range of Cells

[ryguy7272]

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---

 

[Gary''s Student]

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.

 

[T. Valko]

Try an array formula** like this:

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

The "x" is case sensitive.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ryguy7272]

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---

 

[Jarek Kujawa]

=AVERAGE(IF(A25:A40="NA","",SUBSTITUTE(A25:A40,"x",)*1))

this is an array formula so CTRL+SHIFT+ENTER this formula rather than
just use Enter

 

[Gary''s Student]

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.

 

[ryguy7272]

It wasn’t a calculated N/A; someone just stuck “NA†in a few cells. Jarek’s
solution worked. I thank you very much!! One more thing…I wrapped
everything in Text, as such:
=TEXT(AVERAGE(IF(A25:A40="NA","",SUBSTITUTE(A25:A40,"x",)*1)),"#.##")&"x"

Only did that because those calculated average results we giving me way more
precision than what I need for my business.

Thanks everyone!!
Ryan--


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

Try an array formula** like this:

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

The "x" is case sensitive.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[ryguy7272]

New request; just a bit different. I have this kind of setup in several cells:
="(-30.5)"

This will eliminate the left parentheses:
=SUBSTITUTE(AD8,CHAR(40),"")

This gets rid of the right:
=SUBSTITUTE(AD8,CHAR(41),"")

How, in the world, do I put them together, and get an average?

I tried this, committed with CSE:
=AVERAGE(--SUBSTITUTE(AD8:AD12,CHAR(40),""))--SUBSTITUTE(AD8:AD12,CHAR(41),"")

Thanks,
Ryan---


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

It wasn’t a calculated N/A; someone just stuck “NA†in a few cells. Jarek’s
solution worked. I thank you very much!! One more thing…I wrapped
everything in Text, as such:
=TEXT(AVERAGE(IF(A25:A40="NA","",SUBSTITUTE(A25:A40,"x",)*1)),"#.##")&"x"

Only did that because those calculated average results we giving me way more
precision than what I need for my business.

Thanks everyone!!
Ryan--

 

[T. Valko]

Array entered.

Assuming no empty cells.

=AVERAGE(--SUBSTITUTE(SUBSTITUTE(A1:A4,"(",""),")",""))

If there might be empty cells:

=AVERAGE(IF(A1:A4<>"",--SUBSTITUTE(SUBSTITUTE(A1:A4,"(",""),")","")))

 

[ryguy7272]

Biff, that is amazing! Your functions must take everything before the final
quiote and convert all the stuff to numeric values. Very impressive. I was
going about it totally the wrong way.

Thanks so much!
Ryan----

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Jarek Kujawa]

welcome
thanks!

It wasn’t a calculated N/A; someone just stuck “NA†in a few cells.  Jarek’s
solution worked.  I thank you very much!!  One more thing…I wrapped
everything in Text, as such:
=TEXT(AVERAGE(IF(A25:A40="NA","",SUBSTITUTE(A25:A40,"x",)*1)),"#.##")&"x"

Only did that because those calculated average results we giving me way more
precision than what I need for my business.

Thanks everyone!!
Ryan--

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






- Pokaż cytowany tekst -