Excel: Using MID function in a concantenated range

[Pro33]

I am having no luck using the mid function inside a concantenation. I am trying to find the median and average inside a concantenation using the mid function:
AM
101110
231123
123411

=median(mid(am1:am3,3,2))
????

 

[Gavona]

Sorry, hard to follow what you are endeavouring to do. Mid's first parameter is a text value (string) not a range. Not sure why you want to concatenate, what part of your function you think is doing the concatenating or what your mid function is intended to return.

Also are you doing this in VBA (code) or in the workbook itself? Forum707 is for VBA whereas Forum68 is a more appropriate place to post non-code questions.

Gavin

 

[Pro33]

I've created a concatenation in AM. I now need to take values in each string and find the median of those values. I am using the mid function to take out the value but I need to do it for an entire column.

 

[Gavona]

Ok, I just realised you are using an array formula - I rarely do myself so didn't realise at first esp since the requirement seems odd.

Your problem is that Mid() returns a string not a value. Force Excel to consider the result as a number. E.g. try:
=median([r]--[/r]mid(am1:am3,3,2))
Entered as an Array formula using CTRL+SHIFT+ENTER



Gavin