average with N/A

[dernspiker]

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!

 

[Eduardo]

Hi,

=IFERROR(AVERAGE(Sheet2:Sheet3!A2),AVERAGE(Sheet2:Sheet3!A2))
I supposed that you have sheet 2 and 3 and the cell to average is A2

If this helped please click yes. Thank you

 

[dernspiker]

This is my formula then:
=IFERROR(AVERAGE(Adams:Webb!B4),AVERAGE(Adams:Webb!B4))

But now it returns a #NAME? error

 

[Dave Peterson]

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))