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!

Hi,

Try this

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

D4:D7 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

"ericaamousseau" wrote in message
...
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!

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?

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

--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in message
...
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!