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