Sweet- thanks!
Keith

"Dave F" wrote in message
...
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
--
Brevity is the soul of wit.


"Keith" wrote:

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