average from multiple worksheet using if is number
 

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!

average from multiple worksheet using if is number
 

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!

average from multiple worksheet using if is number
 

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?

average from multiple worksheet using if is number
 

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