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: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:I3 4))
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!

Try this:

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

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

Biff

"tafoyavision" wrote in message
...
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: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:I3 4))
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?

"tafoyavision" wrote in
message
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: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:I3 4))
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:



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

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

"Alan" wrote:

"tafoyavision" wrote in
message
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: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:I3 4))
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:



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

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

"Biff" wrote:

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

"tafoyavision" wrote in message
...
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: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:I3 4))
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?

You're welcome. Thanks for the feedback!

Biff

"tafoyavision" wrote in message
...
it worked perfectly! thanks so much...you guys really help people, thank
you

"Biff" wrote:

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

"tafoyavision" wrote in message
...
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: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:I3 4))
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?