Need to average data if in cells, but ignore blank cells.

[Paula]

1. I am averaging several columns, but when all blanks, comes back #DIV/)!.
2. I then have to average the rows, so when a row has a number in it and a
#DIV/)! in it, the return I get is #DIV/0!.
3. What I need it to do is the columns return a blank cell when I average
them if they are blank. I need the rows to return the average on the numbers
only and return a blank cell if no numbers in that row. Also all of this info
is carried with other info onto a master sheet. This is what it looks like:

1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag
97% 97%
94% 94%
100% 100%
#DIV/0!
97% #DIV/0! #DIV/0! #DIV/0! #DIV/0!

 

[RagDyeR]

Try this type of formula:

=AVERAGE(IF(INDEX(A22<>"",),A22))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

1. I am averaging several columns, but when all blanks, comes back #DIV/)!.
2. I then have to average the rows, so when a row has a number in it and a
#DIV/)! in it, the return I get is #DIV/0!.
3. What I need it to do is the columns return a blank cell when I average
them if they are blank. I need the rows to return the average on the
numbers
only and return a blank cell if no numbers in that row. Also all of this
info
is carried with other info onto a master sheet. This is what it looks like:

1st Quarter 2nd Quarter 3rd Quarter 4th Quarte AnnualAverag
97% 97%
94% 94%
100% 100%
#DIV/0!
97% #DIV/0! #DIV/0! #DIV/0! #DIV/0!

 

[Paula]

RD,
I did what you suggested, but it returned a 0%, so then when averaged, it
averaged in the 0%. Any suggestions?

Thank you.

 

[Bernie Deitrick]

Use this kind of formula in place of all your average formulas:

=IF(COUNT(B2:B4)>0,AVERAGE(B2:B4),"")

HTH,
Bernie
MS Excel MVP

 

[Paula]

Thank you Bernie Deitrick-that did it!!

Use this kind of formula in place of all your average formulas:

=IF(COUNT(B2:B4)>0,AVERAGE(B2:B4),"")

HTH,
Bernie
MS Excel MVP