I would use 9 cells (on a separate (hidden???) worksheet:

='Smith'!B4
='Jones'!b4
....
='Zydecker'!b4

Then use a 10th cell to get the average.

=average(if(isnumber(a1:a9),a1:a9))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

or this formula (normally entered):

=sumif(a1:a9,"<1E37")/count(a1:a9)

1E37 is a very large number (1 followed by 37 zeros).

Or maybe:

=if(count(a1:a9)=0,"No numbers",sumif(a1:a9,"<1E37")/count(a1:a9))



dernspiker wrote:

I have a spreadsheet with 9 worksheets (named by employee last name).
On the 9th worksheet I want to get the average of cells B4 from each of the
other sheets. Some of these cells may include an N/A.

Can you give me a formula that will overlook the N/A cells?

Thank you!

--

Dave Peterson