Digits inside numbers and applying formulas

[Peggy1974]

I have a series of numers like this in A1-A3:

976543
287530
296743

Now I want to calculate the combined average of the 3th, 4th and 5th digits in those numbers. So in this example that's the average of 654, 753 and 674 (693.6 in this example).
Right now I'm doing this in 2 steps: first =VALUE(MID(A1;3;3)) and dragging the fill handle down, then I'm calculating the average of the combined results.
I wonder if there's one single formula to calculate all of this.

Sorry for what is probably a dumb and obvious question

 

[gaz_chops]

Try

=SUMPRODUCT((MID(A1:A3,3,3))/COUNT(A1:A3))

 

[Peggy1974]

This unfortunatly seems to give me a "formula" error. Doesn't "MID" convert automatically numbers to text? Maybe has something to do with that.

 

[gaz_chops]

I get

[TABLE="width: 95"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]976543[/TD]
[TD="align: right"]693.7[/TD]
[/TR]
[TR]
[TD="align: right"]287530[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]296743[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[barry houdini]

Looks like you need semi-colon separators for your region, try this version of gaz's suggested formula

=SUMPRODUCT(MID(A1:A3;3;3)/COUNT(A1:A3))

You could also use AVERAGE like this:

=AVERAGE(MID(A1:A3;3;3)+0)

...but that latter formula needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

 

[Peggy1974]

The first suggestion seems to do the trick, think I'll stick with that one.
Thanks for the help.

 

[gaz_chops]

Thanks Barry, I missed that! Is the array option better? I wasn't sure which one to suggest, so went with the none array?

 

[gaz_chops]

You're welcome, I missed that you where using ; as separators.

 

[Peggy1974]

Another question expanding on the previous example. What if I want to calculate the average of the literal reverse of those numbers. So instead of 654, 753 and 674 this becomes the average of 456, 357 and 476 in the example above.
Is it still possible to do the whole exercise (so from the beginning in the opening post) in one single formula? Thanks in advance for the help.

 

[gaz_chops]

Try

=SUMPRODUCT((MID(A1:A3;5;1)&MID(A1:A3;4;1)&MID(A1:A3;3;1))/COUNT(A1:A3))

Also, in answer to your comment about mid converting numbers to text, this is correct but, by adding (or dividing etc) a number it will convert it back to a number.
So your original formula =VALUE(MID(A1;3;3)) could be amended to =MID(A1;3;3)+0

HTH

Gaz