What's wrong with my three formulas here?

Hi,

I'm basically an Excel noob but I thought I had this particular sheet worked out. I just looked at it again and it obviously isn't providing the right numbers. I've attached an anonymised sheet with the problem columns NOT coloured in... So 3 questions for any Excel heroes out there:

1) Why isn't the formula in column C giving me the first number from the string in column B?

2) Why isn't the formula in column F giving me the total number of reports for that particular area by counting only the numbers in row D that correspond to the correct string in column A?

3) How do I change the average formula in column G to do the average sum I need it to do (shown in the working column in italics?

Any help much appreciated - I thought I had it sussed but very obviously not..

1st question: I don't know why, and I'm not going to try to figure it out.
A much simpler method to return the first number in a string exists

In C2, try
=MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),1)

2nd question

Because the formula in F2 is referring to the ENTIRE COLUMN references of A and D
So F2 is counting/summing the cells you added in your 'Should Look Like' Section.
If you restrict F2 to only look at rows 2:11, then you'll get the correct results.
=SUMIF($A$2:$A$11,E2,$D$2:$D$11)

Question 3)
The MID function in Column C returns a TEXT string, even if it looks like a number.
The AVERAGEIF function will see it as TEXT and will be considered 0.

Change C2 to
=IFERROR(MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),1)+0,0)

AND observe the same problem of Question 2, not referring to the entire sheet including the 'should look like' section.

Thanks one and all. Much appreciated.

You're welcome.

I've added reputation for your both - only picked your post up this morning when I came back to check the thread.