average from multiple worksheet using if is number

[ericaamousseau]

I can not figure this out! I need to average multiple rows of data in 4
different worksheets. The biggest problem is that the data is calculated
from time differences, so #Value! is common because of using NA instead of a
time. So say I need J2:J46 in worksheet one J2:J46 in worksheet 2 J2:J46 in
worksheet 3 and J2:J46 in worksheet 4 all averaged together. Also there will
be some non value numbers. Help!

 

[Ashish Mathur]

Hi,

Try this

=SUMPRODUCT(SUMIF(INDIRECT(J2:J46&"!D47"),">-9.999E307"))/SUMPRODUCT(COUNTIF(INDIRECT(J2:J46&"!D47"),">-9.999E307"))

D47 holds the sheet names to the summed up - in your case, worksheet 1,
worksheet 2 , worksheet 3 and worksheet 4

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[arjen van...]

If you're using Excel 2007 you can use the AVERAGEIF function.

=AVERAGEIF(J2:J46,"<>#N/A")

In this method the count that the average is based on will not include the
N/A cells. Should they be?

 

[T. Valko]

#Value! is common because of using NA instead of a time.

AVERAGE ignores text entries when they are part of a referenced array.

Also there will be some non value numbers

What are non value numbers?

Did you try something like this:

=AVERAGE(Sheet1:Sheet4!J2:J46)

Or, if the sheets are random:

=AVERAGE(Sheet1!J2:J46,Sheet5!J2:J46,Sheet7!J2:J46,Sheet10!J2:J46)