averaging averages?

[tafoyavision]

I have 3 worksheets, where on one I enter data (numbers), on the second I
would average those numbers with the exact formula =AVERAGE('1'!F:F). Now on
this worksheet there is a whole column of these for instance the formula
right below the one above is =AVERAGE('1'!K:K), =AVERAGE('1'!P), etc. On
the third worksheet I would like to average this whole row but I keep getting
the dreaded #div/0! error. I first tried =AVERAGE(magic3!I4:I34) then I tried
=IF(COUNT(magic1!I4:I34)=0,"",AVERAGE(magic1!I4:I34))
after lurking around here for some answers but same error happens. How can I
average cells that have formulas in them and that aren't always going to have
data?

 

[Biff]

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(IF(ISNUMBER(Magic!I4:I34),Magic!I4:I34))

Biff

 

[Alan]

I have 3 worksheets, where on one I enter data (numbers), on the
second I would average those numbers with the exact formula
=AVERAGE('1'!F:F). Now on this worksheet there is a whole column of
these for instance the formula right below the one above is
=AVERAGE('1'!K:K), =AVERAGE('1'!P), etc. On the third worksheet I
would like to average this whole row but I keep getting the dreaded
#div/0! error. I first tried =AVERAGE(magic3!I4:I34) then I tried
=IF(COUNT(magic1!I4:I34)=0,"",AVERAGE(magic1!I4:I34))
after lurking around here for some answers but same error happens.
How can I average cells that have formulas in them and that aren't
always going to have data?

Hi,

I see you already have a reply on the mechanics of your query, but I
would just add a note that you have to be careful averaging averages -
it can be mathematically dubious.

For example:

If I have data set A:

Average(1,9) = 5

Average(2,2,2,2) = 2


But,

Average(1,9,2,2,2,2) = 3

does not equal

Average(5,2) = 3.5


Just so you are aware.

Alan.

--
The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

(e-mail address removed)

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address

 

[tafoyavision]

Thanks to both of u...it worked perfectly!, man, I love having smart people
as a resource. Thanks again

 

[tafoyavision]

it worked perfectly! thanks so much...you guys really help people, thank you

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

it worked perfectly! thanks so much...you guys really help people, thank
you