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


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

Advertisements

R

RagDyeR

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

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.
 
B

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
 
Ad

Advertisements

P

Paula

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top