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

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!

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

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

"Paula" wrote in message
...
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!

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

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

Thank you.

"RagDyeR" wrote:

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

"Paula" wrote in message
...
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!

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

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" wrote in message
...
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!

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

Thank you Bernie Deitrick-that did it!!

"Bernie Deitrick" wrote:

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" wrote in message
...
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!