Is it possible

[carley465]

So I have a spreadsheet that has in each cell a code that looks like the
following "NYL5". What I want to do is take the 4th character in this
code with is always number and add them all together in a column and
then calculate the average. Is this possible at all? If so can you tell
me how?

Thanks,

stuck

 

[Dave Peterson]

To get the average:

=AVERAGE(--MID(a1:a20,4,1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

And to get the sum (if you really want):

=sum(--MID(a1:a20,4,1))
(still an array formula)

 

[Ken Macksey]

Assume cell a1 = NYL3 Cell b1 formula is =VALUE(RIGHT(A1,1))
cell a2 = NYL5 Cell b2 formula is
=VALUE(RIGHT(A2,1))
cell a3 = NYL7 Cell b3 formula is
=VALUE(RIGHT(A3,1))

cell c1 formula is =SUM(B1:B3)


This should get you started.


HTH

Ken

 

[Ken Macksey]

Sorry , I missed the average part when I read it, but I see you have 2
replies now anyway.

Ken

 

[RagDyer]

Hey Bob,

Is your clock self-winding?
Maybe you shook it too much?
It's running sooooo fast.<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

=AVERAGE(IF(A1:A25<>"",VALUE(RIGHT(A1:A25,1))))

which is an array form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[carley465]

Ok so cell A1 is NYH1 cell A2 is NYH1, cell A3 is YYM1S, Cell B1 i
NYH1, Cell B2 is YYM1S and cell B3 is YYM1S. I need to take the 4t
character which is always numeric and get and average for each column
I am think I have to put in hidden rows figure out the numeric value o
these cells then average. Yes? No? Is there an easier way?

still stumped

 

[Bob Phillips]

I think it's all okay now RD, I forgot to change time zones.

Bob

 

[xpnovice]

Hi,

I'm no expert, but the following works for me:

Assuming the NYL5 is in cell A1, enter the following in cell B1 (or any cell
you require)

=INT(RIGHT(A1,1))

this will return the value 5, (autofill this over any other required cell
range) then its just a matter of using the average function over the range
of the cells containing the numerical values to get your average.

Hope it works for you

JohnH

 

[Bob Phillips]

My formula, slightly modified works okay

=AVERAGE(IF(A1:B25<>"",VALUE(RIGHT(A1:B25,1))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

=AVERAGE(IF(A1:A25<>"",VALUE(RIGHT(A1:A25,1))))

which is an array form ula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)