averaging range across sheets after transforming text to value

[Keith]

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

 

[Keith]

Sweet- thanks!
Keith

One way to avoid including 0 values in average calculations is to combine
SUMIF and COUNTIF.

Since average is defined as the sum of the items in a set divided by the
count of the items in the set, doing something like
=SUM(A1:A10)/COUNTIF(A1:A10,>0) will sum the range and divide by the
number
of items greater than 0.

Dave

 

[Keith]

I guess I don't have it yet....

This is very close:
=SUM(VALUE(LEFT(Sheet1:Sheet3!D27,1)))/COUNTA(Sheet1:Sheet3!D27)

but it the first part of the formula is not evaluating properly, e.g.
=SUM(VALUE(LEFT(Sheet1!D27,1))) returns the value of 3 (as expected)
but
=SUM(VALUE(LEFT(Sheet1:Sheet3!D27,1)))
still returns a #REF, because the blank cells (currently in sheet2 and
sheet3) can't return a left character, which must return an error instead of
a "non-value" that could be ignored by the value/sum statement

I've tried putting in an if statement, which I can then expand to include
the formula above (limiting it to only cells with contents)
=SUM(IF(LEN(sheet1:sheet3!E27)>0,1,0))
but it gives a #REF error. Interestingly, using the same formula with only
one sheet (with or without a value) returns either 0 or 1 as expected
=SUM(IF(LEN(sheet1!E27)>0,1,0)) =1 ("3. MA/MS")
=SUM(IF(LEN(sheet3!E27)>0,1,0)) =0 (blank cell)

but it won't sum these values when I try to use the range reference across
the three sheets [=SUM(IF(LEN(sheet1:sheet3!E27)>0,1,0))], either as a
regular or array formula.

Is there a better way for me to approach this?
Many thanks,
Keith