Calculate Average with 'x' in Range of Cells

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

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---
 
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.
 
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.
 
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---
 
=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
 
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.
 
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''.


T. Valko said:
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.
Click to expand...
 
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''.


ryguy7272 said:
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--
Click to expand...
 
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,"(",""),")","")))
 
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----
 
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 -
Click to expand...
 
Back
Top