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

  • Thread starter Thread starter Paula
  • Start date Start date
P

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!
 
Try this type of formula:

=AVERAGE(IF(INDEX(A2:D2<>"",),A2:D2))

--

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!
 
RD,
I did what you suggested, but it returned a 0%, so then when averaged, it
averaged in the 0%. Any suggestions?

Thank you.
 
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
 
Thank you Bernie Deitrick-that did it!!

Bernie Deitrick said:
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
Click to expand...
 
Back
Top