I have a number of sheets, where each sheet will be "owned" by a person who
will enter data in that sheet. I've used data validation and lists wherever
possible to make it easy to pull summary information together.
Unfortunately, I have one area where the source list is text and I need to
pull summary data. Although the text has to be part of the list, I prefixed
the text with a numeric representation as follows:

1. high school
2. BA/BS
3. MA/MS
4. Ph.D./JD

Now I need to pull together a representation of what was entered in each
sheet, so I try to grab the leftmost char (1 to 4) and average them.
However, this gives me an error, presumably because it is always likely that
at least one sheet will be blank, so it can't grab the leftmost char to
average it.

For example, if sheet1 has "2. BA/BS" and sheet2 has "3. MA/MS", and sheet3
doesn't have anything selected (empty cell) I want my formula to return a
value of 2.5

my current formula is: (entered as regular or array, neither works)

=AVERAGE(VALUE(LEFT(Sheet1:Sheet3!D27,1)))

I also tried: (regular and array)
=AVERAGE(IF(LEN(Kevin:Catherine!D27)0,VALUE(LEFT( Kevin:Catherine!D27,1)),0))

but not only does it not work, I think that averaging the last zero is
misrepresenting- I don't want to stick a zero value in where no entry was
made, I just want to average the entries that were made.

I appreciate any help you can give me!
Thanks,
Keith