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

 

[Peggy1974]

Alright, that seems to work. Thanks again.
And thanks for the explanation on MID - VALUE.

 

[gaz_chops]

You're welcome.

 

[Peggy1974]

One final question.

Similar situation as in OP

976543
287530
296743
733008

No avarages this time but now I want to calculate based on the 3th, 4th and 5th digit of each number how many resulting numbers are > 700, how many are between 650 and 700 and how many are below 650 (in this example that should be 1 (the second number 753), 2 (the first and 3th 654 & 674) and 1 (the 4th 300)). Is it still possible to put all of this in one formula?

 

[gaz_chops]

If this is in one formula, how do you want the result displaying?

 

[Peggy1974]

I'd like the results to show how many numbers there are in each seperate category.

In the example above:

>700: 1
650-700: 2
<650: 1

 

[gaz_chops]

I thought you meant all in one formula, like "1 - 2 - 1".
Try

>700

[FONT=Lucida Grande]=SUMPRODUCT([/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]>700[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]*ISNUMBER[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]

650-700

[FONT=Lucida Grande]=SUMPRODUCT([/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]<=700[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]*[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]>=650[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]

<650

[FONT=Lucida Grande]=SUMPRODUCT([/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]<650[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]*ISNUMBER[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]

 

[Peggy1974]

Thanks!

I thought you meant all in one formula, like "1 - 2 - 1".

Well that would be even better if that's a possibility.

 

[gaz_chops]

LOL it's easy, just a case of concatenating the 3 formulas

[FONT=Lucida Grande]=SUMPRODUCT([/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]>700[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]*ISNUMBER[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])&" - "&SUMPRODUCT([/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]<=700[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]*[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]>=650[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])&" - "&SUMPRODUCT([/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][FONT=Lucida Grande]--MID[/FONT][COLOR=#AB30D6][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][FONT=Lucida Grande],3,3[/FONT][COLOR=#AB30D6][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]<650[/FONT][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande]*ISNUMBER[/FONT][COLOR=#006107][FONT=Lucida Grande]([/FONT][/COLOR][COLOR=#0057D6][FONT=Lucida Grande]A1:A10[/FONT][/COLOR][COLOR=#006107][FONT=Lucida Grande])[/FONT][/COLOR][FONT=Lucida Grande])[/FONT]

 

[Peggy1974]

Sundays and thinking are usually a bad combination for me.
Thanks again!

 

[gaz_chops]

You're welcome.