Calculate Average with 'x' in Range of Cells
 

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

Calculate Average with 'x' in Range of Cells
 

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

Calculate Average with 'x' in Range of Cells
 

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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''.

Calculate Average with 'x' in Range of Cells
 

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

Calculate Average with 'x' in Range of Cells
 

=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



On 8 Kwi, 18:24, 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''.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Calculate Average with 'x' in Range of Cells
 

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

Calculate Average with 'x' in Range of Cells
 

It wasnt a calculated N/A; someone just stuck €œNA€� in a few cells. Jareks
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:A4 0,"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" wrote:

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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''.

Calculate Average with 'x' in Range of Cells
 

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" wrote:

It wasnt a calculated N/A; someone just stuck €œNA€� in a few cells. Jareks
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:A4 0,"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" wrote:

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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''.

Calculate Average with 'x' in Range of Cells
 

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
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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" wrote:

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:A4 0,"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" wrote:

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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''.

Calculate Average with 'x' in Range of Cells
 

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



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


"T. Valko" wrote:

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
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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" wrote:

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:A4 0,"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" wrote:

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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''.

Calculate Average with 'x' in Range of Cells
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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----



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


"T. Valko" wrote:

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
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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" wrote:

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:A4 0,"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" wrote:

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.

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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''.

Calculate Average with 'x' in Range of Cells
 

welcome
thanks!

On 8 Kwi, 20:08, ryguy7272
wrote:
It wasnt a calculated N/A; someone just stuck €œNA€� in a few cells. Â*Jareks
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:A4 0,"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" wrote:
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.

--
Biff
Microsoft Excel MVP

"ryguy7272" wrote in message
...
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''.- Ukryj cytowany tekst -

- Pokaż cytowany tekst -