Calculate Average with 'x' in Range of Cells

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

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

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

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

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
 
G

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

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


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

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


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

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,"(",""),")","")))
 
R

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

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 -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top